SQL Aggregate Functions
Tweet |
SQL Aggregate functions return a single value, using values in a table column. In this chapter we are going to introduce a new table called Sales, which will have the following columns and data:
OrderID | OrderDate | OrderPrice | OrderQuantity | CustomerName |
1 | 12/22/2005 | 160 | 2 | Smith |
2 | 08/10/2005 | 190 | 2 | Johnson |
3 | 07/13/2005 | 500 | 5 | Baldwin |
4 | 07/15/2005 | 420 | 2 | Smith |
5 | 12/22/2005 | 1000 | 4 | Wood |
6 | 10/2/2005 | 820 | 4 | Smith |
7 | 11/03/2005 | 2000 | 2 | Baldwin |
The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. If we want to count how many orders has made a customer with CustomerName of Smith, we will use the following SQL COUNT expression:
Let's examine the SQL statement above. The COUNT keyword is followed by brackets surrounding the * character. You can replace the * with any of the table's columns, and your statement will return the same result as long as the WHERE condition is the same.
The result of the above SQL statement will be the number 3, because the customer Smith has made 3 orders in total.
If you don't specify a WHERE clause when using COUNT, your statement will simply return the total number of rows in the table, which in our case is 7:
How can we get the number of unique customers that have ordered from our store? We need to use the DISTINCT keyword along with the COUNT function to accomplish that:
The SQL SUM function is used to select the sum of values from numeric column. Using the Sales table, we can get the sum of all orders with the following SQL SUM statement:
As with the COUNT function we put the table column that we want to sum, within brackets after the SUM keyword. The result of the above SQL statement is the number 4990.
If we want to know how many items have we sold in total (the sum of OrderQuantity), we need to use this SQL statement:
The SQL AVG function retrieves the average value for a numeric column. If we need the average number of items per order, we can retrieve it like this:
Of course you can use AVG function with the WHERE clause, thus restricting the data you operate on:
The above SQL expression will return the average OrderQuantity for all orders with OrderPrice greater than 200, which is 17/5.
The SQL MIN function selects the smallest number from a numeric column. In order to find out what was the minimum price paid for any of the orders in the Sales table, we use the following SQL expression:
The SQL MAX function retrieves the maximum numeric value from a numeric column. The MAX SQL statement below returns the highest OrderPrice from the Sales table: