SQL Server GROUP BY and HAVING - Aggregate Reports Tutorial

Understanding GROUP BY

GROUP BY is used in SQL Server to group rows that have the same values in specified columns. This grouping allows aggregate functions to compute summaries for each group.

Without GROUP BY, aggregate functions operate on the entire result set. GROUP BY breaks the data into subsets for more granular analysis.

• Syntax: SELECT columns, aggregate_function(column) FROM table GROUP BY columns;
• Common aggregate functions: COUNT, SUM, AVG, MIN, MAX
• All non-aggregated columns in SELECT must appear in GROUP BY

Using HAVING to Filter Groups

HAVING filters groups created by GROUP BY based on aggregate conditions. It works like WHERE but applies after grouping.

You cannot use WHERE to filter aggregated results because WHERE filters rows before aggregation.

• Syntax: SELECT columns, aggregate_function(column) FROM table GROUP BY columns HAVING aggregate_condition;
• HAVING can use aggregate functions in its condition, e.g., HAVING SUM(Amount) > 1000
• Use HAVING to exclude groups that don't meet criteria

Practical Aggregate Report Example

Let's combine GROUP BY and HAVING in a practical example using a fictional Orders table.