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 setDISTINCTCOUNT()count all the unique records in that columnMIN()finds the minimum value in the setMAX()finds the maximum value in the setSUM()sums all the values in the setAVG()computes the arithmetic mean of all the values in the set
- appear in a
SELECTclause and process all rows that satisfy theWHEREclause 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 BYclause consists ofGROUP BYkeyword- and one or more columns
- query computes the aggregate function separately, and returns one row, for each group
- appears between the
WHEREclause, if any, and theORDER BYclause SELECTclause 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 ProductIDHAVING Clause
The HAVING clause is used with the GROUP BY clause to filter group results.
- used to exclude values
- optional
- follows
GROUP BYclause - precedes
ORDER BYclause
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
NULLvalues - arithmetic operators return
NULLwhen either operand isNULL- so may produce unexpected results with aggregate functions
- e.g.,
SUM(Salary) + SUM(Bonus)≠SUM(Salary + Bonus)- assuming some null values