Aggregate Functions

SQL comes with aggregate functions that can help to summarise and generate descriptive statistics for a dataset.

To demonstrate these functions, we will be using the classicmodels database, which you can download from mysqltutorial.

MIN

MIN returns the minimum value of a column.

SELECT
   MIN(buyPrice) 
FROM
   products;

MIN(buyPrice)
15.91

MAX

MAX returns the maximum value of a column.

SELECT
   MAX(buyPrice) 
FROM
   products;

MAX(buyPrice)
103.42

SUM

SUM returns the sum of values in a column.

SELECT
   SUM(buyPrice) 
FROM
   products;

SUM(buyPrice)
5983.47

COUNT and AVG are a little more involved as they ignore NULL values. To demonstrate this, we first alter the table so that buyPrice can be nullable and we insert an entry with a NULL buyPrice.

ALTER TABLE `classicmodels`.`products` CHANGE COLUMN `buyPrice` `buyPrice` DECIMAL(10, 2) NULL, CHANGE COLUMN `MSRP` `MSRP` DECIMAL(10, 2) NULL;

INSERT INTO
   `products`(`productCode`, `productName`, `productLine`, `productScale`, `productVendor`, `productDescription`, `quantityInStock`, `buyPrice`, `MSRP`) 
VALUES
   (
      'S72_7777', '1995 Boeing 777-200ER', 'Planes', '1:72', 'Second Gear Diecast', 'Polished finish. Exact replia with official logos and insignias and retractable wheels.', 5416, NULL, NULL
   )
;

COUNT

COUNT(*) returns the number of rows in a table, while COUNT of a column returns the number of non-NULL entries in that column.

SELECT
   COUNT(*) 
FROM
   products;

COUNT(*)
111

SELECT
   COUNT(buyPrice) 
FROM
   products;

COUNT(buyPrice)
110

AVG

The AVG function sums the non-NULL values in a column, and divides that sum by the count of non-NULL values. As the AVG function ignores NULL values, you will notice that attempting to calculate the average of a column by manually summing the column and dividing that sum by the total number of rows will lead to a lower number as this approach has a larger denominator.

SELECT
   AVG(buyPrice),
   SUM(buyPrice) / COUNT(*) AS 'calculatedAvg' 
FROM
   products;

AVG(buyPrice)	calculatedAvg
54.395182	53.905135