👉What is the SQL GROUP BY Clause?
SQL GROUP BY & HAVING: The GROUP BY clause is used in SQL to group rows with the same values and perform aggregate calculations such as COUNT(), SUM(), AVG(), etc. It helps in summarizing data and is commonly used in reporting and analytics.
SQL GROUP BY Syntax
sql
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
- SELECT column_name, aggregate_function(column_name) – Retrieves specific columns and applies an aggregate function.
- FROM table_name – Specifies the table from which data is retrieved.
- GROUP BY column_name – Groups records by the specified column(s).
- HAVING condition – Filters grouped results (similar to WHERE, but for grouped data).
👉Example 1: Grouping Data by a Single Column
SQL GROUP BY & HAVING: To count the number of male and female members:
sql
SELECT gender, COUNT(membership_number)
FROM members
GROUP BY gender;
Result:
| gender | COUNT(membership_number) |
| Female | 3 |
| Male | 5 |
🔹 Explanation:
- GROUP BY gender groups data by gender.
- COUNT(membership_number) counts the number of members in each gender category.
👉Example 2: Grouping by Multiple Columns
SQL GROUP BY & HAVING: To list movie categories and their release years:
sql
SELECT category_id, year_released
FROM movies
GROUP BY category_id, year_released;
Result:
| category_id | year_released |
| 1 | 2011 |
| 2 | 2008 |
| 8 | 2007 |
| 8 | 2005 |
🔹 Explanation:
- GROUP BY category_id, year_released groups results by category and year.
- Duplicate entries are removed, leaving only unique combinations.
👉Example 3: Using GROUP BY with Aggregate Functions
SQL GROUP BY & HAVING: To find the total number of movies in each category:
sql
SELECT category_id, COUNT(movie_id) AS total_movies
FROM movies
GROUP BY category_id;
Result:
| category_id | total_movies |
| 1 | 5 |
| 2 | 8 |
| 8 | 12 |
👉Using the HAVING Clause to Filter Grouped Data
SQL GROUP BY & HAVING: Unlike WHERE, which filters before grouping, HAVING filters after the grouping operation.
Example 4: Filtering Grouped Data with HAVING
To get only categories with more than 5 movies:
sql
SELECT category_id, COUNT(movie_id) AS total_movies
FROM movies
GROUP BY category_id
HAVING COUNT(movie_id) > 5;
Result:
| category_id | total_movies |
| 2 | 8 |
| 8 | 12 |
🔹 Explanation:
- GROUP BY category_id groups movies by category.
- HAVING COUNT(movie_id) > 5 filters results after aggregation.
👉Why Use GROUP BY & HAVING in SQL?
✅ Summarizing Data – Easily generate reports (e.g., total sales per region).
✅ Filtering Aggregated Results – Use HAVING to set conditions on grouped data.
✅ Data Analytics – Calculate averages, counts, sums, and percentages.
✅ Efficient Queries – Optimize database performance by grouping data at query time.
👉Summary
Summary
✔ The GROUP BY clause groups rows with identical values.
✔ It is often used with aggregate functions (COUNT(), SUM(), AVG()).
✔ The HAVING clause filters grouped results, while WHERE filters before grouping.
✔ GROUP BY helps in summarizing large datasets for reports & analytics.
Using GROUP BY and HAVING, you can efficiently analyze and filter data in SQL queries!
