Aggregate Functions in SQL


An aggregate function processes values from a set of rows and returns a summary value.

  • common ones:
    • COUNT() counts the number of rows in the set
    • DISTINCTCOUNT() count all the unique records in that column
    • MIN() finds the minimum value in the set
    • MAX() finds the maximum value in the set
    • SUM() sums all the values in the set
    • AVG() computes the arithmetic mean of all the values in the set
  • appear in a SELECT clause and process all rows that satisfy the WHERE clause condition

GROUP BY Clause

The GROUP BY clause will organize data into groups that can then have aggregated values calculated when you execute the statement.

  • GROUP BY clause consists of
    • GROUP BY keyword
    • and one or more columns
  • query computes the aggregate function separately, and returns one row, for each group
  • appears between the WHERE clause, if any, and the ORDER BY clause
  • SELECT clause may not contain columns with more than one value per group
SELECT ProductID, AVG(StarRating) AS [Avg Rating], COUNT(CT_Rates) AS [Total Count]
FROM ToyStarRatings
GROUP BY ProductID

HAVING Clause

The HAVING clause is used with the GROUP BY clause to filter group results.

  • used to exclude values
  • optional
  • follows GROUP BY clause
  • precedes ORDER BY clause
SELECT ProductID, AVG(StarRating) AS [Avg Rating], COUNT(CT_Rates) AS [Total Count]
FROM ToyStarRatings
GROUP BY ProductID
HAVING (ProductID = 15)

Aggregate Functions and NULL Values

  • aggregate functions ignore NULL values
  • arithmetic operators return NULL when either operand is NULL
    • so may produce unexpected results with aggregate functions
    • e.g.,
      • SUM(Salary) + SUM(Bonus) ≠ SUM(Salary + Bonus)
      • assuming some null values