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

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 rows
• SUM() – total of numeric values
• AVG() – average of numeric values
• MIN() – minimum value
• MAX() – maximum value

When to Use GROUP BY in SQL

GROUP BY 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 the GROUP BY SQL query 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: COUNT GROUP BY

SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;

This GROUP BY query shows how many books exist in each genre.

Example 2: 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 GROUP BY scenario.

Example 3: GROUP BY Multiple Columns

SELECT department, job_title, COUNT(*) AS employees
FROM employees
GROUP BY department, job_title;

This GROUP BY query groups data by both department and job title for a more detailed breakdown.

Example 4: Query Count by Group

SELECT status, COUNT(*) AS total
FROM support_tickets
GROUP BY status;

This query counts tickets grouped by their status (e.g., open, closed, pending).

Example 5: GROUP BY AS Labeling

SELECT region AS sales_region, SUM(amount) AS revenue
FROM transactions
GROUP BY region;

Using GROUP BY lets you rename fields for easier reporting.

Learn More About GROUP BY

GROUP BY Clause with JOIN

You can use GROUP BY 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 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.