SQL COUNT, SUM and AVG Functions

The SQL COUNT, SUM and AVG functions aid in the summarising of data. The COUNT function provides a count of the number of records in a table that match the specified criteria. The SUM function works on numeric data and provides a total where the stated criteria is met. Finally, the AVG function, which again works on numeric data, calculates an average of the values that satisfy the given criteria.

The format for these functions is similar to the MIN and MAX functions discussed previously. As with MIN and MAX, the ‘where’ clause can be omitted if the result required covers all the data in the table.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;

The ‘person_order‘ table, from previous examples, can be used to illustrate how these functions work.

person_order
id order_date order_total person_id
1 2016-01-19 101.60 1
2 2016-02-02 256.54 2
3 2016-03-05 222.10 4
4 2016-05-23 12.45 1
5 2016-07-29 325.65 2

COUNT Function

Below is an example of how the COUNT function can be used to find the total number of orders in the ‘person_order’ table.

SELECT COUNT(id)
FROM person_order;

This will return a result of five, counting each record once.

Instead of specifying a column to count on, an asterisk can be used in its place.

SELECT COUNT(*)
FROM person_order;

Note that, if a count of orders by person was required, then a ‘group by‘ statement would need to be used. This is discussed in the next section.

The ‘distinct’ keyword can be used in conjunction with the COUNT function if it is required to count the number of unique values in a particular column. To use the ‘person_order’ table as an example, ‘distinct’ can be used to find the number of unique customers that have placed an order, based on the ‘person_id’ column. This will produce a result of three.

SELECT COUNT(DISTINCT person_id)
FROM person_order;

SUM Function

The SUM function can be used to return a total, based on a numerical column. The ‘order_total’ column, from the ‘person_order’ table, can be used to illustrate this.

SELECT SUM(order_total)
FROM person_order;

Similar to the COUNT function, the ‘distinct’ keyword can be used, in conjunction with the SUM function, to produce a total based on the unique values in a particular column of data. In the case of the ‘order_total’ column of the ‘person_order’ table, this will produce the same result as all the values are unique.

SELECT SUM(DISTINCT order_total)
FROM person_order;

As with the COUNT function, if a sum of orders was required by person, it would need a ‘group by‘ statement, which is discussed in the next section.

AVG Function

If it is necessary to find the average of a particular set of numerical values, then the AVG function can be used. With the ‘person_order’ table, the AVG function can return an average ‘order_total’.

SELECT AVG(order_total)
FROM person_order;

As with both the COUNT and SUM function, the ‘distinct’ keyword can be used to return an average based on unique values, which, in the case of the ‘order_total’ column of the ‘person_order’ table, will produce the same result, as all the values are unique.

SELECT AVG(DISTINCT order_total)
FROM person_order;

If an average order total was required by person, this again would need a ‘group by‘ statement, discussed in the next section, similar to both the COUNT and SUM functions.