Aggregate Functions
Aggregate functions like SUM, COUNT, AVG, MIN, and MAX collapse many rows into a single value. COUNT(*) counts all rows including those with NULLs in any column, while COUNT(ColumnName) counts only rows where that specific column is non-NULL — a subtle but important distinction when auditing data completeness. AVG and SUM also ignore NULL values entirely rather than treating them as zero, which can skew results if you expect NULLs to pull an average down.
Cricket analogy: COUNT(*) on a Deliveries table counts every ball bowled in an innings, but COUNT(ExtraType) only counts balls flagged as wides or no-balls, since legal deliveries have a NULL ExtraType — and AVG(RunsScored) ignores balls with no recorded runs rather than treating them as zero.
SELECT
Region,
COUNT(*) AS TotalOrders,
COUNT(DiscountCode) AS OrdersWithDiscount,
SUM(TotalAmount) AS RevenueSum,
AVG(TotalAmount) AS AvgOrderValue
FROM Sales.Orders
GROUP BY Region
ORDER BY RevenueSum DESC;GROUP BY and HAVING
GROUP BY collapses rows sharing the same value(s) in specified columns into one row per group, and every non-aggregated column in the SELECT list must appear in the GROUP BY clause, or SQL Server raises an error. HAVING filters groups after aggregation, unlike WHERE, which filters individual rows before grouping — so WHERE TotalAmount > 100 removes rows before summing, while HAVING SUM(TotalAmount) > 10000 removes entire groups whose total falls below the threshold.
Cricket analogy: GROUP BY Team on a BattingScorecard collapses every player's innings into one row per team's total, and HAVING SUM(Runs) > 300 keeps only the teams that posted a 300-plus total, filtering after the runs are already summed, unlike WHERE which would exclude individual low-scoring innings beforehand.
Grouping by multiple columns produces one row per unique combination — GROUP BY Region, ProductCategory creates a separate summary row for each Region/Category pair. ROLLUP and CUBE extend GROUP BY to produce subtotal and grand-total rows automatically, and GROUPING_ID or the GROUPING() function distinguishes a genuine NULL data value from the NULL SQL Server inserts to represent a subtotal row.
Cricket analogy: GROUP BY Team, Venue produces one row per team-venue combination, like India's record at the MCG versus at Eden Gardens, and adding WITH ROLLUP appends subtotal rows for each team across all venues plus one grand total row.
Use ROLLUP when you need a hierarchical set of subtotals (e.g. by Region then Region+Category), and CUBE when you need subtotals for every possible combination of the grouping columns, including combinations that skip a column entirely.
Every non-aggregated column referenced in the SELECT list, HAVING clause, or ORDER BY must also appear in GROUP BY, otherwise SQL Server raises error 8120 ('column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause').
- COUNT(*) counts all rows; COUNT(column) counts only non-NULL values in that column.
- SUM and AVG silently ignore NULL values rather than treating them as zero.
- GROUP BY collapses rows into one row per unique combination of grouped columns.
- HAVING filters groups after aggregation; WHERE filters rows before aggregation.
- Every non-aggregated SELECT column must appear in GROUP BY.
- ROLLUP adds hierarchical subtotal and grand-total rows; CUBE adds all-combination subtotals.
- GROUPING()/GROUPING_ID() distinguish real NULL data from ROLLUP/CUBE-generated subtotal NULLs.
Practice what you learned
1. What is the difference between COUNT(*) and COUNT(DiscountCode)?
2. Which clause filters groups after aggregation has occurred?
3. What happens if a SELECT list includes a non-aggregated column not listed in GROUP BY?
4. What does AVG(TotalAmount) do with rows where TotalAmount is NULL?
5. What is the main purpose of ROLLUP in a GROUP BY clause?
Was this page helpful?
You May Also Like
SELECT and Filtering
Learn how to retrieve exactly the rows and columns you need from SQL Server tables using SELECT, WHERE, and related filtering clauses.
Joins in T-SQL
Understand how INNER, OUTER, CROSS, and self joins combine rows from multiple tables in SQL Server, and how to avoid common join pitfalls.
Window Functions
Learn how OVER, PARTITION BY, and ranking/analytic functions let you compute running totals, rankings, and row-to-row comparisons without collapsing rows.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics