SQL GROUP BY and HAVING - Real Examples Tutorial
Quick Answer
The SQL GROUP BY clause groups rows sharing a property so aggregate functions can be applied. HAVING filters these grouped results based on aggregate conditions. Together, they enable powerful data summarization and filtering in SQL queries.
Learning Objectives
- Understand the purpose and syntax of the GROUP BY clause in SQL.
- Learn how to use HAVING to filter grouped data based on aggregate conditions.
- Apply GROUP BY and HAVING clauses in practical SQL queries with real examples.
Introduction
SQL GROUP BY and HAVING clauses are essential for summarizing and filtering grouped data.
GROUP BY organizes rows sharing common values, enabling aggregate functions like COUNT, SUM, AVG, MAX, and MIN.
HAVING filters these grouped results based on aggregate conditions, which WHERE cannot do.
Grouping data is the key to insightful summaries.
Understanding GROUP BY
The GROUP BY clause groups rows that have the same values in specified columns.
It is commonly used with aggregate functions to calculate summaries per group.
- Syntax: SELECT columns, aggregate_function(column) FROM table GROUP BY columns;
- Groups rows sharing the same values in the GROUP BY columns.
- Aggregate functions operate on each group separately.
Example: Counting Orders per Customer
Suppose you have an orders table and want to count how many orders each customer placed.
Using HAVING to Filter Groups
HAVING filters groups created by GROUP BY based on aggregate conditions.
Unlike WHERE, which filters rows before grouping, HAVING applies conditions after aggregation.
- Syntax: SELECT columns, aggregate_function(column) FROM table GROUP BY columns HAVING condition;
- Commonly used to filter groups by COUNT, SUM, AVG, etc.
Example: Customers with More Than 5 Orders
To find customers who placed more than 5 orders, use HAVING with COUNT.
Real-World Examples
Let's explore practical SQL queries using GROUP BY and HAVING.
Example 1: Total Sales per Product
Calculate total sales amount for each product in a sales table.
Example 2: Products with Sales Over $10,000
Filter products whose total sales exceed $10,000 using HAVING.
Example 3: Average Employee Salary by Department
Find departments with an average salary greater than $70,000.
Practical Example
This query groups orders by customer_id and counts the number of orders per customer.
This query filters customers who have placed more than 5 orders using HAVING.
This query sums sales amounts grouped by product_id.
This query filters products with total sales exceeding $10,000.
This query finds departments where the average salary is greater than $70,000.
Examples
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;This query groups orders by customer_id and counts the number of orders per customer.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;This query filters customers who have placed more than 5 orders using HAVING.
SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_id;This query sums sales amounts grouped by product_id.
SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(sale_amount) > 10000;This query filters products with total sales exceeding $10,000.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;This query finds departments where the average salary is greater than $70,000.
Best Practices
- Always include all non-aggregated columns in the GROUP BY clause.
- Use HAVING only to filter aggregated data, not individual rows.
- Test queries with GROUP BY and HAVING on small datasets first.
- Use meaningful aliases for aggregated columns for clarity.
Common Mistakes
- Using WHERE to filter aggregated results instead of HAVING.
- Omitting columns in GROUP BY that appear in SELECT without aggregation.
- Confusing the order of WHERE and HAVING clauses in queries.
- Applying HAVING without GROUP BY, which is invalid in most SQL dialects.
Hands-on Exercise
Count Orders per Product
Write a query to count how many orders each product has in the orders table.
Expected output: A list of product IDs with their corresponding order counts.
Hint: Use GROUP BY product_id and COUNT(*) aggregate function.
Filter Departments by Average Salary
Find departments where the average salary is above $60,000.
Expected output: Departments with average salaries greater than $60,000.
Hint: Use GROUP BY department_id and HAVING AVG(salary) > 60000.
Identify High-Selling Customers
List customers who have total sales greater than $15,000.
Expected output: Customer IDs with total sales exceeding $15,000.
Hint: Use GROUP BY customer_id and HAVING SUM(sale_amount) > 15000.
Interview Questions
What is the difference between WHERE and HAVING in SQL?
InterviewWHERE filters rows before grouping, while HAVING filters groups after aggregation.
Can you use HAVING without GROUP BY?
InterviewIn most SQL dialects, HAVING requires GROUP BY because it filters grouped data.
Why must all non-aggregated columns in SELECT appear in GROUP BY?
InterviewBecause SQL needs to know how to group rows consistently for those columns.
MCQ Quiz
1. What is the best first step when learning Real Examples?
A. Understand the purpose and basic idea
B. Skip directly to advanced implementation
C. Ignore examples and practice
D. Memorize terms without context
Correct answer: A
Starting with the purpose and basic idea makes later examples and practice easier to understand.
2. Which activity helps reinforce Real Examples?
A. Reading once without practice
B. Building or writing a small practical example
C. Avoiding review questions
D. Skipping the summary
Correct answer: B
A small practical example helps connect the topic to real usage.
3. Which statement is most accurate about this topic?
A. The SQL GROUP BY clause groups rows sharing a property so aggregate functions can be applied.
B. Real Examples never needs examples
C. Real Examples is unrelated to practical work
D. Real Examples should be learned without checking results
Correct answer: A
The correct option is based on the available topic explanation.
Key Takeaways
- GROUP BY groups rows to perform aggregate calculations on each group.
- HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping.
- Using GROUP BY and HAVING together allows detailed data summarization and conditional filtering.
- The SQL GROUP BY clause groups rows sharing a property so aggregate functions can be applied.
- HAVING filters these grouped results based on aggregate conditions.
Summary
SQL GROUP BY groups rows sharing common values to enable aggregate calculations per group.
HAVING filters these groups based on aggregate conditions, allowing refined summaries.
Mastering GROUP BY and HAVING is essential for effective data analysis and reporting in SQL.
Frequently Asked Questions
Can I use multiple columns in GROUP BY?
Yes, you can group by multiple columns to create more specific groups.
Is HAVING mandatory when using GROUP BY?
No, HAVING is optional and used only when you want to filter groups based on aggregates.
What happens if I use WHERE with aggregate functions?
WHERE cannot filter on aggregate functions; you must use HAVING for that purpose.
