Common SQL Aggregate Functions

COUNT(): Counts the number of rows in a dataset. It can also count non-null values in a specific column.

SUM(): Adds up the values in a numerical column.

AVG(): Calculates the average value of a numerical column.

MAX() and MIN(): Determine the maximum and minimum values in a column, respectively.

Usage with GROUP BY

Aggregate functions are often used in conjunction with the GROUP BY clause to perform calculations on grouped sets of data. This is useful when you want to apply an aggregate function to each group separately.

Using with HAVING

While the WHERE clause is used to filter rows before grouping, the HAVING clause is used to filter groups after the GROUP BY has been applied. It is commonly used with aggregate functions.

Restrictions and Notes

• WHERE vs. HAVING: WHERE is applied before the data is aggregated, and therefore cannot use aggregate functions. HAVING is applied after the aggregation and can be them.
• Aggregate functions ignore NULL values, except for COUNT(*), which counts rows regardless of null values in them.
• It's also useful to use aggregate functions with sorting (ORDER BY) to organize the output.