GROUP BY collapses multiple rows sharing the same value(s) in the grouped column(s) into one output row per group, and every other selected column must either be part of the GROUP BY list or wrapped in an aggregate function — this is a real rule most engines enforce (Postgres and SQL Server reject the query outright; MySQL historically allowed it and picked an arbitrary row's value, which is almost never what you want).
SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary, MAX(salary) AS top_salary
FROM Employees
GROUP BY dept;
The standard aggregate functions and what they do with NULL:
| Function | Behavior | NULL handling |
|---|---|---|
COUNT(*) | Counts rows | Counts every row, NULL columns included |
COUNT(column) | Counts non-NULL values in that column | Skips NULL |
SUM(column) | Adds values | Skips NULL; SUM of an all-NULL group is NULL, not 0 |
AVG(column) | Mean of values | Skips NULL — divides by the count of non-NULL rows, not total rows |
MIN / MAX | Smallest/largest value | Skips NULL |
SUM() returning NULL (not 0) when every value in a group is NULL catches people who then try to compare that result with > or < — remember three-valued logic applies here too, so HAVING SUM(salary) > 0 silently drops a group whose sum came back NULL.