When working with databases, you often need more than just individual records. In many real-world applications, you need to summarize and analyze data.

For example:

• How many customers are from each city?
• What is the total sales amount for each product?
• What is the average salary in each department?
• How many orders were placed on each date?
• Which category generated the highest revenue?

SQL’s GROUP BY clause is designed for these types of tasks.

The GROUP BY clause groups rows that have the same value in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to produce useful summaries and reports.

In this complete guide, you will learn what SQL GROUP BY is, how it works, its syntax, aggregate functions, multiple-column grouping, WHERE and HAVING, ORDER BY, JOIN, NULL values, common mistakes, performance considerations, and practical examples.

---

What Is SQL GROUP BY?

The SQL GROUP BY clause is used to group rows that have the same values in one or more columns.

For example, suppose you have a customers table:

id	name	city
1	Rahul	Jaipur
2	Priya	Delhi
3	Amit	Jaipur
4	Neha	Mumbai
5	Ravi	Delhi

If you want to know how many customers belong to each city, you can use:

SELECT city, COUNT(*) AS total_customers
FROM customers
GROUP BY city;

Result

city	total_customers
Jaipur	2
Delhi	2
Mumbai	1

Instead of returning every customer individually, SQL groups customers by their city and calculates the number of rows in each group.

---

Why Is GROUP BY Important?

GROUP BY is especially useful when you need to create reports, summaries, statistics, dashboards, and analytics from database data.

For example, an e-commerce application might need:

• Total orders per customer
• Sales per month
• Revenue per category
• Products sold per supplier
• Average order value
• Number of users per country

Without GROUP BY, these calculations would be much harder to organize.

---

SQL GROUP BY Syntax

The basic syntax is:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

For example:

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

This groups employees according to their department and counts the employees in each department.

---

GROUP BY with COUNT()

COUNT() is one of the most commonly used aggregate functions with GROUP BY.

Suppose you have:

Orders

order_id	customer_id	status
101	1	Completed
102	2	Completed
103	1	Pending
104	3	Completed
105	1	Completed

To count orders by customer:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

Result

customer_id	total_orders
1	3
2	1
3	1

---

COUNT(*) vs COUNT(column)

There is an important difference between:

COUNT(*)

and:

COUNT(column_name)

COUNT(*) counts rows.

COUNT(column_name) counts non-NULL values in that column.

For example:

SELECT city, COUNT(phone) AS phone_count
FROM customers
GROUP BY city;

This counts only customers who have a non-NULL phone number.

---

GROUP BY with SUM()

SUM() calculates the total of a numeric column.

Suppose you have:

order_id	customer_id	amount
101	1	2000
102	2	1500
103	1	3000
104	3	2500

To calculate total spending per customer:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Result

customer_id	total_spent
1	5000
2	1500
3	2500

This is very useful for sales and revenue reports.

---

GROUP BY with AVG()

AVG() calculates the average value.

For example, to find the average salary by department:

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Example Result

department	average_salary
IT	65000
HR	48000
Sales	55000

---

GROUP BY with MIN()

MIN() returns the smallest value in each group.

SELECT
    department,
    MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;

This shows the lowest salary in each department.

---

GROUP BY with MAX()

MAX() returns the largest value in each group.

SELECT
    department,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

This returns the highest salary for each department.

---

Common Aggregate Functions with GROUP BY

The most common aggregate functions are:

Function	Purpose
COUNT()	Counts rows or non-NULL values
SUM()	Calculates a total
AVG()	Calculates an average
MIN()	Finds the minimum value
MAX()	Finds the maximum value

These functions are frequently combined with GROUP BY.

---

GROUP BY Multiple Columns

You can group data using more than one column.

Suppose you have:

id	city	gender
1	Jaipur	Male
2	Jaipur	Female
3	Jaipur	Male
4	Delhi	Female
5	Delhi	Male

You can group by both city and gender:

SELECT
    city,
    gender,
    COUNT(*) AS total_users
FROM users
GROUP BY city, gender;

Result

city	gender	total_users
Jaipur	Male	2
Jaipur	Female	1
Delhi	Female	1
Delhi	Male	1

SQL creates groups based on the combination of the two columns.

---

GROUP BY Three or More Columns

You can also group by three or more columns.

SELECT
    country,
    city,
    gender,
    COUNT(*) AS total_users
FROM users
GROUP BY country, city, gender;

This creates a group for each unique combination of country, city, and gender.

However, avoid grouping by unnecessary columns because it can create many small groups and make reports harder to understand.

---

GROUP BY with WHERE

WHERE and GROUP BY are frequently used together.

WHERE filters individual rows before grouping.

For example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
WHERE salary > 50000
GROUP BY department;

The query first considers employees earning more than 50,000 and then groups those employees by department.

Conceptual Flow

Employees
   ↓
WHERE salary > 50000
   ↓
Filtered Employees
   ↓
GROUP BY department
   ↓
Department Summary

---

GROUP BY with HAVING

HAVING is used to filter groups after aggregation.

For example, suppose you want departments containing more than five employees:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

The query first creates groups and calculates their counts. Then HAVING removes groups that do not satisfy the condition.

---

WHERE vs HAVING

This is one of the most important concepts when learning GROUP BY.

WHERE

Filters individual rows.

WHERE salary > 50000

HAVING

Filters groups.

HAVING COUNT(*) > 5

Example

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 60000;

Conceptually:

1. Keep active employees.
2. Group them by department.
3. Calculate average salary.
4. Keep departments whose average salary is greater than 60,000.

---

GROUP BY with ORDER BY

You can sort grouped results using ORDER BY.

For example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC;

This displays departments with the largest number of employees first.

You can also use the aggregate expression:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;

Using the alias is often easier to read when supported by your database.

---

GROUP BY with SUM and ORDER BY

Suppose you want to find which customers spent the most money:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

This creates a ranking of customers by total spending.

---

GROUP BY with LIMIT

You can combine grouping and limiting to find top results.

For example:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

This can return the five customers with the highest total spending in databases that support LIMIT.

Other database systems may use different syntax for limiting rows.

---

GROUP BY with JOIN

GROUP BY becomes especially powerful when combined with JOIN.

Suppose you have:

Customers

customer_id	name
1	Rahul
2	Priya
3	Amit

Orders

order_id	customer_id	amount
101	1	2000
102	2	1500
103	1	3000
104	3	2500

To calculate total spending for each customer:

SELECT
    c.name,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Result

name	total_spent
Rahul	5000
Priya	1500
Amit	2500

---

GROUP BY with LEFT JOIN

Suppose you want to include customers who have never placed an order.

Use LEFT JOIN:

SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Using COUNT(o.order_id) rather than COUNT(*) is important here.

For a customer with no matching order:

• COUNT(*) counts the preserved customer row.
• COUNT(o.order_id) counts only non-NULL order IDs and therefore returns 0.

This is a very common reporting pattern.

---

GROUP BY with DISTINCT

DISTINCT and GROUP BY can sometimes appear to produce similar results, but they serve different purposes.

For example:

SELECT DISTINCT city
FROM customers;

returns unique cities.

You can also write:

SELECT city
FROM customers
GROUP BY city;

Both can return one row per city.

However, GROUP BY becomes especially useful when you want to calculate aggregate values:

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city;

---

GROUP BY with NULL Values

SQL treats NULL values as belonging to the same grouping category for GROUP BY.

Suppose:

id	city
1	Jaipur
2	Delhi
3	NULL
4	Jaipur
5	NULL

Query:

SELECT
    city,
    COUNT(*) AS total
FROM customers
GROUP BY city;

The NULL values will be grouped together.

The result may look conceptually like:

city	total
Jaipur	2
Delhi	1
NULL	2

---

GROUP BY with Date Values

Grouping dates is common in reporting systems.

Suppose you have an orders table:

order_id	order_date	amount
1	2026-08-01	1000
2	2026-08-01	2000
3	2026-08-02	1500

You may want total sales per day.

A database-specific date expression can be used:

SELECT
    order_date,
    SUM(amount) AS daily_sales
FROM orders
GROUP BY order_date;

If order_date contains timestamps rather than just dates, you may need a database-specific function to extract the date portion.

For example, syntax differs among MySQL, PostgreSQL, SQL Server, and Oracle.

---

GROUP BY by Month

Monthly sales reports are extremely common.

The exact syntax depends on the database.

For example, in MySQL:

SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

This groups sales by year and month.

In PostgreSQL, you might use:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

Always use the syntax appropriate for your database system.

---

GROUP BY with CASE

CASE can be useful when you want to create custom groups.

For example, you could classify products based on price:

SELECT
    CASE
        WHEN price < 1000 THEN 'Budget'
        WHEN price < 5000 THEN 'Mid Range'
        ELSE 'Premium'
    END AS price_category,
    COUNT(*) AS total_products
FROM products
GROUP BY
    CASE
        WHEN price < 1000 THEN 'Budget'
        WHEN price < 5000 THEN 'Mid Range'
        ELSE 'Premium'
    END;

This creates groups such as:

• Budget
• Mid Range
• Premium

Some database systems also provide ways to group by a derived expression or alias, but repeating the expression is the more broadly portable pattern.

---

GROUP BY with Multiple Aggregate Functions

You can use multiple aggregate functions in the same query.

SELECT
    department,
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

This produces a complete salary summary for every department.

---

GROUP BY Without Aggregate Functions

Although GROUP BY is commonly used with aggregate functions, it can also be used without them.

For example:

SELECT city
FROM customers
GROUP BY city;

This returns one row per distinct city.

However, when your goal is simply to remove duplicates, DISTINCT may communicate your intention more clearly:

SELECT DISTINCT city
FROM customers;

---

GROUP BY and SELECT Rules

One of the most important rules is:

When using GROUP BY, every selected expression that is not aggregated generally needs to be included in the GROUP BY clause.

For example:

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

This is valid.

But this can be invalid or ambiguous in standard SQL:

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

Why?

Because there may be multiple employees in one department, so SQL does not know which name should represent the department.

A correct approach could be:

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

But that creates groups for each department/name combination.

If you want one row per department, you need to decide how the name should be represented—for example, using an aggregate or a separate query.

---

Functional Dependencies and Database-Specific Behavior

Some database systems can allow selecting columns that are functionally dependent on grouped columns.

For example, if customer_id uniquely determines customer_name, certain databases may permit:

SELECT customer_id, customer_name, COUNT(*)
FROM orders
GROUP BY customer_id;

However, behavior and SQL modes vary.

For portable SQL, explicitly include non-aggregated selected columns in the GROUP BY clause unless your database documentation confirms otherwise.

---

GROUP BY vs ORDER BY

GROUP BY and ORDER BY have completely different purposes.

GROUP BY

Groups rows.

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

ORDER BY

Sorts results.

SELECT city, COUNT(*) AS total
FROM customers
GROUP BY city
ORDER BY total DESC;

You can use both in the same query.

---

GROUP BY vs DISTINCT

These two clauses can sometimes produce similar results, but they are not interchangeable in purpose.

DISTINCT

Used to remove duplicate result rows:

SELECT DISTINCT city
FROM customers;

GROUP BY

Used to create groups and perform aggregate calculations:

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

A simple rule:

Use DISTINCT when you want unique results. Use GROUP BY when you want grouped calculations or summaries.

---

GROUP BY vs HAVING

Another important distinction:

GROUP BY

Creates groups.

HAVING

Filters those groups.

Example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) >= 10;

Here:

• GROUP BY department creates department groups.
• COUNT(*) counts employees.
• HAVING COUNT(*) >= 10 keeps only departments with at least 10 employees.

---

SQL Logical Processing Order

A simplified conceptual order for a query containing WHERE, GROUP BY, and HAVING is:

FROM
  ↓
JOIN
  ↓
WHERE
  ↓
GROUP BY
  ↓
HAVING
  ↓
SELECT
  ↓
ORDER BY
  ↓
LIMIT

For example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY total_employees DESC;

Conceptually:

1. Read employees.
2. Filter employees earning more than 50,000.
3. Group the remaining employees by department.
4. Count employees in each department.
5. Keep groups with more than five employees.
6. Sort the results.

The database optimizer may physically execute operations in a different order while producing the same logical result.

---

Real-World GROUP BY Examples

Example 1: Customers by City

SELECT
    city,
    COUNT(*) AS total_customers
FROM customers
GROUP BY city;

---

Example 2: Employees by Department

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

---

Example 3: Total Sales by Product

SELECT
    product_id,
    SUM(quantity * price) AS total_sales
FROM order_items
GROUP BY product_id;

---

Example 4: Average Product Price by Category

SELECT
    category_id,
    AVG(price) AS average_price
FROM products
GROUP BY category_id;

---

Example 5: Highest Salary by Department

SELECT
    department,
    MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

---

Example 6: Lowest Product Price by Category

SELECT
    category_id,
    MIN(price) AS lowest_price
FROM products
GROUP BY category_id;

---

Example 7: Total Orders by Status

SELECT
    status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY status;

Possible result:

status	total_orders
Completed	125
Pending	30
Cancelled	15

---

Example 8: Revenue by Customer

SELECT
    customer_id,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY customer_id
ORDER BY total_revenue DESC;

---

Example 9: Active Users by Country

SELECT
    country,
    COUNT(*) AS active_users
FROM users
WHERE status = 'active'
GROUP BY country
ORDER BY active_users DESC;

---

Example 10: Departments with High Average Salary

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY average_salary DESC;

---

GROUP BY in E-Commerce Applications

GROUP BY is extremely useful for e-commerce reporting.

For example, an admin dashboard might need to display:

Total Orders by Status

SELECT
    status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY status;

Revenue by Month

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date);

The exact date syntax depends on your database.

Top Customers

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

These kinds of queries are commonly used to power reports and dashboard metrics.

---

GROUP BY in CRM Systems

CRM applications can use GROUP BY to generate reports such as:

• Leads by source
• Customers by city
• Deals by sales representative
• Opportunities by status
• Revenue by salesperson

Example:

SELECT
    lead_source,
    COUNT(*) AS total_leads
FROM leads
GROUP BY lead_source
ORDER BY total_leads DESC;

---

GROUP BY in ERP Systems

ERP software can use GROUP BY for:

• Sales by department
• Inventory by category
• Purchases by supplier
• Expenses by department
• Employees by location

For example:

SELECT
    supplier_id,
    SUM(quantity) AS total_purchased
FROM purchase_items
GROUP BY supplier_id;

---

GROUP BY Performance Tips

GROUP BY can become expensive when working with millions of rows.

Here are some practical considerations.

1. Filter Early When Appropriate

If you only need data from the current year, filtering can reduce the rows considered by the grouping operation:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

---

2. Index Appropriate Columns

Indexes can help some queries, particularly when filtering or accessing data efficiently.

For example, if your workload frequently filters by:

WHERE order_date >= '2026-01-01'

an appropriate index on order_date may help.

Whether an index improves a particular GROUP BY query depends on the database, data distribution, query plan, and available indexes.

---

3. Avoid Unnecessary Grouping Columns

Instead of:

GROUP BY country, state, city, gender, age, status

only group by the columns actually needed for the report.

More grouping columns generally mean more distinct groups.

---

4. Avoid SELECT *

With aggregation, explicitly select the required columns:

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

This is clearer than attempting to combine SELECT * with grouped results.

---

5. Use EXPLAIN or Execution Plans

For slow queries, examine the database’s execution plan.

Depending on the database, you may use:

EXPLAIN
SELECT
    customer_id,
    SUM(amount)
FROM orders
GROUP BY customer_id;

Some databases also support EXPLAIN ANALYZE for examining actual execution behavior.

---

Common GROUP BY Mistakes

Mistake 1: Selecting a Non-Grouped Column

Potentially invalid:

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

There can be many names in one department.

Instead, decide what result you actually need.

---

Mistake 2: Using WHERE for Aggregate Conditions

Incorrect:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
WHERE COUNT(*) > 5;

The correct clause is HAVING:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

---

Mistake 3: Confusing GROUP BY with ORDER BY

GROUP BY does not primarily sort your data.

This:

GROUP BY department

creates groups.

This:

ORDER BY department

sorts the result.

If you need both, use both clauses.

---

Mistake 4: Using COUNT(*) When You Need COUNT(column)

With a LEFT JOIN, this can produce unexpected counts:

COUNT(*)

If you want the number of matching orders, use:

COUNT(o.order_id)

because COUNT(column) ignores NULL.

---

Mistake 5: Grouping by Too Many Columns

Grouping by unnecessary columns can produce overly detailed results.

For example:

GROUP BY city, name, email, phone

may create one group per customer instead of one group per city.

Always decide what one row in your final result should represent.

---

SQL GROUP BY Cheat Sheet

Requirement	Example
Group by one column	GROUP BY city
Group by multiple columns	GROUP BY city, gender
Count rows	COUNT(*)
Count non-NULL values	COUNT(column)
Calculate total	SUM(amount)
Calculate average	AVG(salary)
Find minimum	MIN(price)
Find maximum	MAX(price)
Filter rows before grouping	WHERE
Filter groups after grouping	HAVING
Sort grouped results	ORDER BY
Limit results	LIMIT where supported

---

Frequently Asked Questions

What is GROUP BY in SQL?

GROUP BY is used to group rows that have the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), and AVG().

---

Why do we use GROUP BY?

It is used to create summarized reports from data.

For example:

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

This tells you how many customers belong to each city.

---

Can GROUP BY use multiple columns?

Yes.

SELECT city, gender, COUNT(*)
FROM users
GROUP BY city, gender;

---

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping.

HAVING filters groups after aggregation.

---

Can GROUP BY be used without aggregate functions?

Yes.

SELECT city
FROM customers
GROUP BY city;

However, DISTINCT may be clearer when the only goal is to remove duplicates.

---

Can GROUP BY be used with JOIN?

Yes.

SELECT
    c.name,
    COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

---

Can GROUP BY be used with ORDER BY?

Yes.

SELECT
    department,
    COUNT(*) AS total
FROM employees
GROUP BY department
ORDER BY total DESC;

---

Does GROUP BY sort the results?

No. GROUP BY creates groups; it should not be relied upon for result ordering.

Use ORDER BY when you need a specific order.

---

What happens to NULL values in GROUP BY?

Rows with NULL in the grouping column are generally grouped together into a NULL group.

---

Conclusion

The SQL GROUP BY clause is an essential tool for data aggregation, reporting, analytics, and database-driven dashboards.

It allows you to transform large amounts of individual records into meaningful summaries.

The most important concepts to remember are:

• GROUP BY creates groups of rows with the same values.
• It is commonly used with COUNT(), SUM(), AVG(), MIN(), and MAX().
• You can group by one or multiple columns.
• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• ORDER BY sorts the final result.
• JOIN can be combined with GROUP BY to create reports from multiple tables.
• COUNT(*) counts rows, while COUNT(column) ignores NULL values.
• NULL values form their own grouping category.
• Avoid unnecessary grouping columns.
• For large datasets, examine indexes and execution plans when performance becomes an issue.

If you are learning SQL, a good progression is:

SELECT → WHERE → JOIN → GROUP BY → HAVING → ORDER BY → Subqueries → Window Functions

Once you understand GROUP BY, you can start building much more powerful sales reports, customer analytics, financial summaries, CRM dashboards, ERP reports, and e-commerce statistics using SQL.