11.1 GROUP BY

Level: 11 | Version: v1.1 | Author: Meptrasoft

Overview

GROUP BY is used to group rows with the same value and create summary results.

It is commonly used with aggregate functions such as COUNT(), SUM(), and AVG().

ROWS
 ↓
GROUP BY
 ↓
GROUPS
 ↓
SUMMARY

GROUP BY → Group similar rows and calculate a summary for each group.

SQL GROUP BY — Group Rows and Summarize — Figure 1: GROUP BY groups rows with the same value and allows aggregate functions such as COUNT() to produce a summary for each group.

Practice Table

This topic uses the customers table:

customer_id	first_name	country
1	John	USA
2	Robert	USA
3	David	UK
4	John	UK
5	Betty	UAE

Basic GROUP BY

Find the number of customers in each country:

SELECT country, COUNT(*) AS number
FROM customers
GROUP BY country;

Result:

country	number
UAE	1
UK	2
USA	2

Instead of returning every customer row, SQL creates one summary row for each country.

GROUP BY with Other Aggregates

GROUP BY can be combined with aggregate functions.

For example:

SELECT country, AVG(age) AS average_age
FROM customers
GROUP BY country;

This calculates the average age separately for each country.

GROUP BY country
        ↓
One group per country
        ↓
AVG(age) for each group

How GROUP BY Works

Suppose the countries are:

USA
USA
UK
UK
UAE

GROUP BY country creates:

USA → 2 rows
UK  → 2 rows
UAE → 1 row

Then an aggregate function can summarize each group.

GROUP BY does not simply sort rows. It creates groups based on matching values.

GROUP BY vs SELECT

A common beginner confusion is:

SELECT country
→ Show country

GROUP BY country
→ Create one group for each country

For example:

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

COUNT(*) is calculated separately for every country group.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`GROUP BY` sorts the result	`ORDER BY` sorts; `GROUP BY` creates groups
`GROUP BY` returns every original row	It usually produces one result row per group when used with aggregates
`GROUP BY` can be used only with `COUNT()`	It can be used with `SUM()`, `AVG()`, `MIN()`, `MAX()`, and others
Each group contains only one row	A group can contain many rows
`GROUP BY country` counts automatically	An aggregate such as `COUNT()` is needed to calculate a count

Placement Quick Points

GROUP BY
→ CREATE GROUPS

COUNT()
→ COUNT EACH GROUP

SUM()
→ TOTAL EACH GROUP

AVG()
→ AVERAGE EACH GROUP

GROUP BY groups rows with the same value.
It is commonly used with aggregate functions.
Each group can produce a summary result.
COUNT(), SUM(), AVG(), MIN(), and MAX() can be used with groups.
GROUP BY is different from ORDER BY.

Interview Questions

What is GROUP BY?

GROUP BY groups rows with the same values so that aggregate calculations can be performed for each group.

Why is GROUP BY commonly used with aggregate functions?

It allows calculations such as count, total, or average to be performed separately for each group.

What is the difference between GROUP BY and ORDER BY?

GROUP BY → Group rows
ORDER BY → Sort rows

Can GROUP BY be used with COUNT()?

Yes.

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

Can GROUP BY be used with AVG()?

Yes.

SELECT country, AVG(age)
FROM customers
GROUP BY country;

Practice & Hands-On Exercises

Using the customers table:

Count customers in each country:

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

Find the average age for each country:

SELECT country, AVG(age) FROM customers GROUP BY country;

Find the minimum age in each country:

SELECT country, MIN(age) FROM customers GROUP BY country;

Find the maximum age in each country:

SELECT country, MAX(age) FROM customers GROUP BY country;

Explain the difference between GROUP BY and ORDER BY.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

ROWS
 ↓
GROUP BY
 ↓
GROUPS
 ↓
AGGREGATE
 ↓
SUMMARY

GROUP BY creates groups of similar rows so aggregate functions can calculate a summary for each group.

End of GROUP BY