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:

SELECT COUNT (*) FROM Sales

WHERE CustomerName = 'Smith'

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

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 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 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