| Lesson 8 | The arithmetic functions |
| Objective | SQL SUM function |
SQL Sum Function
The SUM() function in SQL (Structured Query Language) is an integral component of data analysis, providing the capability to perform aggregation operations on a set of values. Specifically, it calculates the total sum of a numeric column.
The SUM() function operates by taking as its argument the name of a column containing numerical data, and it then adds together all the values in that column. The function ignores NULL values. It's important to note that SUM() is typically used with the GROUP BY clause to segment the sum operation on certain categories or groups of data. Here is the basic syntax of the SUM() function:
SELECT SUM(column_name)
FROM table_name;
In the context of the GROUP BY clause, the syntax would look like this:
SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1;
SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1;
In this case, the SUM(column2) operation would be performed separately for each unique value in column1.
Consider a hypothetical 'sales' table with 'product', 'region', and 'units_sold' columns. If you want to find the total units sold for each product, the query would look something like this:
SELECT product, SUM(units_sold)
FROM sales
GROUP BY product;
SELECT product, SUM(units_sold)
FROM sales
GROUP BY product;
The SQL server would then iterate through the 'sales' table, grouping the data by unique 'product' values. Within each group, it would add up the 'units_sold', returning a dataset with each product and its corresponding total units sold.
The SUM() function is a powerful tool in SQL, allowing database users to conduct quantitative analysis and derive meaningful insights from numerical data. It is a part of a suite of SQL aggregate functions, which also include COUNT(), AVG(), MIN(), and MAX(), and plays a pivotal role in data manipulation and decision-making processes.
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