SQL Functions   «Prev  Next»
 Lesson 8 The arithmetic functions Objective SQL `SUM` function

# SQL Sum Function

## Understand how the `SUM` function works.

When you need to total the values for a column, you can use the `SUM` function. This function adds all the values together and returns the value as a single, unnamed column. You call the function by simply putting the name of the column you want to summarize in parentheses:

```SELECT SUM(MyNumber)
FROM MyTable
```

This results in a new, unnamed column that will contain the total for MyNumber.
One interesting thing about these functions is that they will return a value for each row returned. In other words, suppose you have a table and want to select all the rows and at the same time, get the total of your information in a given column. Here is what the `SELECT` statement might be:
```SELECT MyName, MyAddress, MyNumber, SUM(MyNumber)
FROM MyTable
```

The `SUM` value will be included with each row. That is, for each row returned, you will get back MyName, MyAddress, MyNumber, and the total value for MyNumber in an unnamed column.

### Computing a Column Average (AVG)

The AVG() column function computes the average of a column of data values. As with the SUM() function, the data in the column must have a numeric type. Because the AVG() function adds the values in the column and then divides by the number of values, its result may have a different data type than that of the values in the column. For example, if you apply the AVG() function to a column of integers, the result will be either a decimal or a floating point number, depending on the brand of DBMS you are using. Here are some examples of the AVG() column function: Calculate the average price of products from manufacturer ACI.

```SELECT AVG(PRICE)
FROM PRODUCTS
WHERE MFR_ID = 'ACI';
AVG(PRICE)
-----------
\$804.29
```

Calculate the average size of an order placed by Acme Manufacturing (customer number 2103).
```SELECT AVG(AMOUNT)
FROM ORDERS
WHERE CUST = 2103;
AVG(AMOUNT)
------------
\$8,895.50
```