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