SQL Aggregate Functions






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:

OrderIDOrderDateOrderPriceOrderQuantityCustomerName
112/22/20051602Smith
208/10/20051902Johnson
307/13/20055005Baldwin
407/15/20054202Smith
512/22/200510004Wood
610/2/20058204Smith
711/03/200520002Baldwin


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.


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


SQL Tutorial

SQL Tutorial
SQL Introduction
RDBMS and Tables
SQL SELECT
SQL WHERE
SQL INSERT
SQL DISTINCT
SQL UPDATE
SQL DELETE
SQL ORDER BY
SQL Aggregate Functions
SQL GROUP BY
SQL HAVING
Relations, Keys, and Normalization
SQL JOIN
SQL UNION
SQL Nested Queries
CREATE DATABASE, CREATE TABLE and ALTER TABLE
SQL Views
SQL Indexes
SQL Training
SQL Hosting
SQL Replication
Contact Us
Legal
SQL Resources
Copyright © Art Branch Inc
SQL Tutorial | Bookmark | Contact | Link