The GROUP BY clause in SQL is used to group rows with the same values, often alongside aggregate functions like COUNT, SUM, AVG, etc. The HAVING clause helps filter grouped data based on specific conditions. In this guide, we will explore their syntax, usage, and real-world examples.
๐Table of Contents
- SQL GROUP BY and HAVING Clause: What is the SQL GROUP BY Clause?
- SQL GROUP BY Syntax
- Grouping by a Single Column
- Grouping by Multiple Columns
- Using GROUP BY with Aggregate Functions
- Filtering Grouped Data with the HAVING Clause
- Real-World Examples
- Summary
๐What is the SQL GROUP BY Clause?
SQL GROUP BY and HAVING Clause: The SQL GROUP BY clause is used to group rows with the same values into a single row. It is typically used with aggregate functions to generate summary reports from the database.
Key Features of GROUP BY:
โ
Groups rows that share the same values in specified columns.
โ
Often used with aggregate functions (COUNT, SUM, AVG, MAX, MIN).
โ
Returns one row per group instead of individual rows.
๐SQL GROUP BY Syntax
SQL GROUP BY and HAVING Clause: Hereโs the basic syntax for using GROUP BY in SQL:
sql
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition;
Explanation:
- SELECT column_name, aggregate_function(column_name): Selects the columns to display along with an aggregate function (optional).
- FROM table_name: Specifies the table from which data is fetched.
- WHERE condition: (Optional) Filters data before grouping.
- GROUP BY column_name: Groups the results based on the specified column(s).
- HAVING condition: (Optional) Filters grouped data after aggregation.
๐ Grouping by a Single Column
SQL GROUP BY and HAVING Clause: To understand the GROUP BY clause, letโs take an example using a members table.
Example: List Unique Genders in a Members Table
sql
SELECT gender FROM members GROUP BY gender;
Result:
| gender |
| Female |
| Male |
This groups the results by gender, showing only unique values.
๐ Grouping by Multiple Columns
SQL GROUP BY and HAVING Clause: You can group by more than one column to get more specific groupings.
Example: Group Movies by Category and Release Year
sql
SELECT category_id, year_released
FROM movies
GROUP BY category_id, year_released;
Result:
| category_id | year_released |
| 1 | 2011 |
| 2 | 2008 |
| 6 | 2007 |
| 8 | 2005 |
โ Here, movies are grouped by category and release year, reducing duplicate entries.
๐Using GROUP BY with Aggregate Functions
SQL GROUP BY and HAVING Clause: Aggregate functions help summarize grouped data, making the GROUP BY clause more useful.
Example: Count the Number of Members by Gender
sql
SELECT gender, COUNT(membership_number)
FROM members
GROUP BY gender;
Result:
| gender | COUNT(membership_number) |
| Female | 3 |
| Male | 5 |
โ The COUNT function counts how many male and female members exist in the database.
๐ Filtering Grouped Data with the HAVING Clause
SQL GROUP BY and HAVING Clause: The HAVING clause is used after GROUP BY to filter grouped results based on a condition.
Example: Find Movie Categories with More Than One Release Year
sql
SELECT category_id, COUNT(year_released)
FROM movies
GROUP BY category_id
HAVING COUNT(year_released) > 1;
Result:
| category_id | COUNT(year_released) |
| 8 | 3 |
โ The HAVING clause filters results to only show categories where movies were released in more than one year.
๐Real-World Examples of GROUP BY and HAVING
Example 1: Get Total Sales Per Product
sql
SELECT product_name, SUM(quantity_sold)
FROM sales
GROUP BY product_name;
โ This shows the total sales per product.
Example 2: Find Customers Who Have Placed More Than 5 Orders
sql
SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;
โ Only customers with more than 5 orders are displayed.
Example 3: Find Average Salary Per Department
sql
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
โ This calculates the average salary for each department.
๐Summary
โ
SQL GROUP BY is used to group rows with the same values.
โ
It is commonly used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
โ
GROUP BY can be used with multiple columns for more detailed analysis.
โ
The HAVING clause filters grouped results based on aggregate function conditions.
โ
It is widely used in business reports, data analysis, and summary statistics.
By mastering SQL GROUP BY and HAVING, you can efficiently analyze large datasets and generate meaningful reports!
