- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL GROUP BY: Syntax, Usage, and Examples
The SQL GROUP BY
clause is used to arrange identical data into groups. When paired with aggregate functions like COUNT()
, SUM()
, or AVG()
, it allows you to perform calculations across those grouped records, rather than on the whole table.
How to Use GROUP BY in SQL
The basic form of GROUP BY SQL
consists of selecting one or more columns, applying an aggregate function, and grouping the result set by one or more fields.
Basic Syntax
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;
For example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
This query counts the number of employees in each department.
Supported Aggregate Functions
Common aggregate functions used with SQL GROUP BY
include:
COUNT()
– total number of rowsSUM()
– total of numeric valuesAVG()
– average of numeric valuesMIN()
– minimum valueMAX()
– maximum value
When to Use GROUP BY in SQL
GROUP BY SQL
is essential for summarizing, aggregating, or analyzing grouped data. It’s commonly used in:
Reporting and Dashboards
In reporting, summarizing values by categories is crucial. For example, finding total sales per product category or number of customers per country.
SELECT country, COUNT(*) AS total_customers
FROM customers
GROUP BY country;
This produces a breakdown of customer count by country.
Analyzing Trends or Totals
To track how many orders were placed each day:
SELECT order_date, COUNT(*) AS total_orders
FROM orders
GROUP BY order_date;
You can also use sql query with group by
to identify top performers or flag anomalies.
Combining with Filtering
To see only categories with more than 100 sales:
SELECT category, COUNT(*) AS sales_count
FROM sales
GROUP BY category
HAVING COUNT(*) > 100;
The HAVING
clause filters after grouping, unlike WHERE
, which filters before.
Examples of SQL GROUP BY
Example 1: SQL COUNT GROUP BY
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;
This sql count group by
query shows how many books exist in each genre.
Example 2: SQL SUM GROUP BY
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;
This calculates the total quantity ordered per product, a typical sql sum group by
scenario.
Example 3: SQL GROUP BY Multiple Columns
SELECT department, job_title, COUNT(*) AS employees
FROM employees
GROUP BY department, job_title;
This sql group by multiple columns
query groups data by both department and job title for a more detailed breakdown.
Example 4: SQL Query Count by Group
SELECT status, COUNT(*) AS total
FROM support_tickets
GROUP BY status;
This sql query count by group
counts tickets grouped by their status (e.g., open, closed, pending).
Example 5: GROUP BY AS SQL Labeling
SELECT region AS sales_region, SUM(amount) AS revenue
FROM transactions
GROUP BY region;
Using group by as sql
lets you rename fields for easier reporting.
Learn More About GROUP BY SQL
Group By Clause in SQL with JOIN
You can use GROUP BY SQL
with joins to group across related tables.
SELECT c.name, COUNT(o.id) AS total_orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
This finds the number of orders each customer placed.
GROUP BY and ORDER BY
You can sort the grouped results using ORDER BY
:
SELECT category, COUNT(*) AS total
FROM products
GROUP BY category
ORDER BY total DESC;
This displays the most populated categories first.
GROUP BY and HAVING
When filtering based on an aggregate, use HAVING
:
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;
This returns categories where the average product price is over 100.
Using Aliases in GROUP BY
While SQL doesn’t allow you to use a column alias in the GROUP BY
clause in most engines, you can repeat the expression:
SELECT category AS cat, COUNT(*) AS cnt
FROM products
GROUP BY category;
You must still use category
, not cat
, in the GROUP BY
.
Filtering Before and After GROUP BY
- Use
WHERE
to filter rows before grouping. - Use
HAVING
to filter groups after aggregation.
Example:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE active = 1
GROUP BY department
HAVING AVG(salary) > 50000;
Only active employees are considered, and departments with high average salaries are returned.
SQL Query with Group By and Subquery
You can also use GROUP BY
inside subqueries for more advanced logic:
SELECT department, avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_avg
WHERE avg_salary > 60000;
This gives a cleaner structure for filtering or joining later.
Grouping NULLs
In SQL, GROUP BY
treats NULL
as a valid group:
SELECT manager_id, COUNT(*)
FROM employees
GROUP BY manager_id;
If some employees have no manager (NULL
), they’ll appear in their own group.
Combining GROUP BY with CASE
Use CASE
statements inside grouped queries for more dynamic logic:
SELECT
department,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;
This breaks down the number of male and female employees per department.
The SQL GROUP BY
clause is a must-know tool for summarizing data and powering reports. It’s the foundation for building dashboards, performing analytics, and answering questions like “How many?”, “What’s the average?”, or “What’s the total per category?”. By mastering sql query with group by
, using it across joins, combining it with filtering, and aggregating across multiple dimensions, you unlock a powerful data analysis toolkit within SQL.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.