Summarizing Data

Using Aggregate Functions

Functions that operate on a set of rows to calculate and return a single value. You would use these functions to do the calculations on the server and only return the required value to the client, saving network bandwidth.

The following are common aggregate functions in SQL :

• AVG
• COUNT
• MAX
• MIN
• SUM

AVG() function

AVG() is used to return the average value of a specific column by counting both the number of rows in the table and the sum of their values.

SELECT AVG(prod_price)
FROM Products;

Average for specific condition

We can use WHERE clause to filter the rows and find the average for the specific condition.

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

Column rows containing NULL values are ignored by the AVG() function.

Count() function

Counts the number of rows.

• count(*) - Count the number of rows in a table, whether columns contain values or NULL values.
• count(column) - Count number of rows that have values in a specific column, ignoring NULL values.

The following will return a count of rows including NULL values.

SELECT COUNT(*) AS num_cust
FROM Customers;

The following will only count the rows for the specific column ignoring NULL values.

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

MAX() function

Returns the highest value specified in a column.

SELECT MAX(prod_price) AS max_price
FROM Products;

Column rows with NULL values in them are ignored by the MAX() function.

MIN() function

Finds the minimum value.

SELECT MIN(prod_price) AS min_price
FROM Products;

SUM() function

Returns the sum total of the values in a specific column.

SELECT SUM(quantity)
FROM OrderItems

Aggregates on Distinct Values

The aggregate functions can also be used with distinct values. By default they use all the values when performing the calculations.

Calculate average on distinct values

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*). Similarly, DISTINCT must be used with a column name and not with a calculation or expression

Combining Aggregate Functions

The aggregate functions can all be combined in a single select statement.

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;