SQL Aggregate Functions

SQL Aggregate Functions

SQL Aggregate functions return a single value, using values in a table column. Useful aggregate functions:

• AVG( ) - Returns the average value
• COUNT( ) - Returns the number of rows
• FIRST( ) - Returns the first value
• LAST( ) - Returns the last value
• MAX( ) - Returns the largest value
• MIN( ) - Returns the smallest value
• SUM( ) - Returns the sum

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:

SELECT COUNT (*) FROM Sales WHERE CustomerName = 'Smith'

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:

SELECT COUNT(*) FROM Sales

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:

SELECT COUNT (DISTINCT CustomerName) FROM Sales

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:

SELECT SUM(OrderPrice) FROM Sales

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:

SELECT SUM(OrderQuantity) FROM Sales

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:

SELECT AVG(OrderQuantity) FROM Sales

Of course you can use AVG function with the WHERE clause, thus restricting the data you operate on:

SELECT AVG(OrderQuantity) FROM Sales WHERE OrderPrice > 200

The above SQL expression will return the average OrderQuantity for all orders with OrderPrice greater than 200, which is 17/5.

Best LED TV to Buy Under Rs 15000 in India
Best LED TV to Buy Under Rs 80000 in India

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:

SELECT MIN(OrderPrice) FROM Sales

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:

SELECT MAX(OrderPrice) FROM Sales