| Lesson 8 || The arithmetic functions |
| Objective ||SQL |
SQL Sum Function
Understand how the
SUM function works.
When you need to total the values for a column, you can use the
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:
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)
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.
WHERE MFR_ID = 'ACI';
Calculate the average size of an order placed by Acme Manufacturing (customer number 2103).
WHERE CUST = 2103;