Aggregate Functions, GROUP BY and HAVING in SQL Server
Quick Answer: Aggregate functions calculate values from multiple rows. GROUP BY combines rows into groups. HAVING filters those groups based on their calculated results.
1. Why Do We Need Aggregate Functions and GROUP BY?
Imagine Priya manages a grocery shop with 5,000 customer orders. She needs to prepare a daily sales report for her shop.
She wants the total sales for each product. She also needs the number of orders and average order amount.
How can she calculate these results without checking every order manually?
The problem without aggregate functions
Priya can retrieve every order and calculate totals herself. But this approach sends unnecessary records to the application.
Example 1: Retrieve individual grocery orders
Assume the Orders table contains Priya's six grocery orders.
SELECT
Product,
Amount
FROM Orders
ORDER BY Product;
Expected output
Product Amount ------- ------ Milk 600 Milk 500 Rice 1200 Rice 900 Sugar 800 Sugar 700
The query returns every order correctly. However, Priya still needs to calculate the totals.
- She must calculate totals for every product separately.
- She needs additional logic to count individual orders.
- Average sales require another calculation.
- Finding products above a sales target becomes harder.
Now imagine Priya handling thousands of orders across many products. Manual calculations become difficult to maintain.
We need SQL Server to calculate these values. We also need a way to group related orders.
The solution: Aggregate Functions, GROUP BY and HAVING
Aggregate Function = a function that calculates one result from multiple values.
GROUP BY = a clause that combines rows sharing the same values.
HAVING = a clause that filters groups after aggregation.
Priya can group orders by product and calculate their sales. She can then display only products meeting her sales target.
Animation 1: How GROUP BY and HAVING work
Watch Priya turn six grocery orders into a filtered sales report.
SQL Server groups the six orders, calculates totals and removes products below 1,500.
2. Implement Aggregate Functions and GROUP BY in SQL Server
Aggregate functions help Priya prepare useful sales reports.
SUM() = calculates the total of numeric values.
COUNT() = counts matching rows or non-NULL values.
AVG() = calculates the average of numeric values.
MIN() = returns the smallest value.
MAX() = returns the largest value.
Calculate sales using aggregate functions
Priya needs sales totals, order counts and average amounts. She also wants the smallest and largest order amounts.
Example 2: Group orders and filter product sales
This complete T-SQL example creates sample orders and groups them by product.
DECLARE @Orders TABLE
(
Product VARCHAR(20),
Amount DECIMAL(10, 2)
);
INSERT INTO @Orders VALUES
('Rice', 1200), ('Rice', 900),
('Sugar', 800), ('Sugar', 700),
('Milk', 600), ('Milk', 500);
SELECT
Product,
COUNT(*) AS TotalOrders,
SUM(Amount) AS TotalSales,
AVG(Amount) AS AverageSales,
MIN(Amount) AS MinimumSale,
MAX(Amount) AS MaximumSale
FROM @Orders
GROUP BY Product
HAVING SUM(Amount) >= 1500
ORDER BY TotalSales DESC;
Notice how GROUP BY Product creates separate product groups. Each aggregate function calculates a result for each group.
The HAVING clause keeps products with sales of at least 1,500. Milk is excluded because its total sales are only 1,100.
Expected output
Product TotalOrders TotalSales AverageSales MinimumSale MaximumSale ------- ----------- ---------- ------------ ----------- ----------- Rice 2 2100.00 1050.00 900.00 1200.00 Sugar 2 1500.00 750.00 700.00 800.00
Important: COUNT(*) counts rows, including rows containing NULL values. COUNT(Amount) counts only non-NULL Amount values. Most aggregate functions ignore NULL values.
3. Quick Summary
Without aggregation vs with aggregation
| Without aggregation | With aggregation |
|---|---|
| Returns individual order records. | Calculates summary values. |
| Application calculates totals. | SUM() calculates totals. |
| Grouping needs additional logic. | GROUP BY creates groups. |
| Filtering individual records only. | HAVING filters calculated groups. |
The important terms to remember
- SUM: Adds numeric values.
- COUNT: Counts rows or non-NULL values.
- AVG: Calculates the average.
- GROUP BY: Groups rows sharing common values.
- HAVING: Filters groups using aggregate conditions.
4. Common Mistakes and Best Practices
1. Using WHERE instead of HAVING for aggregate conditions
WHERE cannot directly filter aggregate results such as SUM(Amount).
Better approach: Use WHERE for individual rows and HAVING for groups.
2. Selecting columns without grouping them
SQL Server does not allow unrelated non-aggregated columns in grouped results.
Better approach: Include selected non-aggregated columns in GROUP BY.
3. Confusing COUNT(*) with COUNT(column)
These expressions behave differently when a column contains NULL values.
Better approach: Choose the count expression based on your reporting requirement.
Design tip: Filter unnecessary rows using WHERE before grouping. Then use HAVING to filter the calculated groups.
5. How Aggregate Functions, GROUP BY and HAVING Come Up in Interviews
Question 1: What are aggregate functions in SQL Server?
What the interviewer expects: Explain calculations performed across multiple records.
Strong answer: "Aggregate functions calculate summary values from multiple rows. Common examples include SUM, COUNT, AVG, MIN and MAX. I use them for reports and business calculations."
Question 2: What is the difference between WHERE and HAVING?
What the interviewer expects: Explain row filtering and group filtering.
Strong answer: "WHERE filters individual rows before grouping. HAVING filters grouped results after aggregate calculations. For example, I use HAVING to find products with total sales above 1,500."
Question 3: Why do we use GROUP BY?
What the interviewer expects: Explain grouping and summary calculations.
Strong answer: "GROUP BY combines rows with matching values. I can group grocery orders by product and calculate each product's total sales."
6. Hands-On Practice
Beginner: School examination results
Help Lakshmi, a teacher, calculate student counts and average marks for each class.
Expected result: Display each class with its student count and average marks.
Intermediate: Hospital billing report
Group hospital bills by department. Calculate total billing and average bill amount.
Hint: Use GROUP BY, SUM() and AVG().
Advanced: Grocery sales analysis
Extend Priya's sales report with a minimum order count requirement.
- Group orders by product.
- Calculate total sales and order counts.
- Keep products with at least two orders.
- Keep only products with sales above 1,500.
Challenge: Combine WHERE, GROUP BY and HAVING in one query.
7. Next Steps and Community
Previous article: SQL Joins – INNER, LEFT, RIGHT, FULL and CROSS JOIN.
Next article: Subqueries and Correlated Subqueries in SQL Server.
For more details, read the official Microsoft documentation:
Try Priya's grocery sales report with your own sample data. Share your queries and discuss different reporting solutions with other learners.