Blog · MySQL / SQL · 04 Jul 2026 · Hindi + English

GROUP BY in SQL with Examples (COUNT, SUM, AVG)

GROUP BY folds rows into one row per group so COUNT/SUM/AVG can answer per-class, per-city questions. Step-by-step with visible intermediate results.

What GROUP BY actually does

Imagine 500 answer sheets lying on a table. The principal asks: "How many students per class, and what's each class's average?" You would first sort the sheets into class-wise piles, then count and average each pile. GROUP BY is exactly the pile-making step: it folds many rows into one row per group, so aggregate functions (COUNT, SUM, AVG, MAX, MIN) can answer per-pile questions.

Step by step with visible piles

-- students table
+-------+-------+-------+
| name  | class | marks |
| Aman  | 10    |  92   |
| Priya | 10    |  88   |
| Rahul | 9     |  45   |
| Neha  | 9     |  95   |
| Kabir | 9     |  60   |
+-------+-------+-------+

SELECT class, COUNT(*) AS students, AVG(marks) AS average
FROM students
GROUP BY class;

-- What MySQL does internally:
-- Pile "10": (Aman 92)(Priya 88)          -> count 2, avg 90.0
-- Pile "9" : (Rahul 45)(Neha 95)(Kabir 60) -> count 3, avg 66.67

+-------+----------+---------+ | class | students | average | | 10 | 2 | 90.00 | | 9 | 3 | 66.67 | +-------+----------+---------+

Five rows became two — one per pile. That folding is the entire concept.

The golden rule (source of the most common error)

Every column in SELECT must be either inside the GROUP BY or inside an aggregate function. Why? Because each output row represents a whole pile — asking for name is asking "which ONE name for the class-9 pile?" There are three names; MySQL cannot pick honestly.

SELECT class, name, AVG(marks)      -- name is neither grouped nor aggregated
FROM students GROUP BY class;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'name'...

This exact error message is what you will meet first in real work — now you know why it exists.

Practical patterns you will actually use

-- 1. Fee collected per class
SELECT class, SUM(amount) AS collected
FROM fees GROUP BY class;

-- 2. Topper's marks per class
SELECT class, MAX(marks) AS topper_marks
FROM students GROUP BY class;

-- 3. Group by TWO columns: one pile per class+section combo
SELECT class, section, COUNT(*) AS strength
FROM students GROUP BY class, section;

-- 4. Only groups meeting a condition -> HAVING
SELECT class, AVG(marks) AS avg_marks
FROM students
GROUP BY class
HAVING AVG(marks) > 70;

-- 5. Find duplicate mobile numbers (classic real-world query!)
SELECT mobile, COUNT(*) AS times
FROM students
GROUP BY mobile
HAVING COUNT(*) > 1;

Pattern 5 deserves a pause: GROUP BY + HAVING COUNT(*) > 1 is the standard technique to find duplicates in any table — asked in interviews and needed in every real project.

Where GROUP BY sits in the pipeline

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
        (filter    (make       (filter
         rows)      piles)      piles)

WHERE trims sheets before piling; HAVING removes whole piles after. Keeping this pipeline in your head answers half of all GROUP BY questions automatically.

GROUP BY असल में करता क्या है

Table पर 500 answer sheets पड़ी हैं. Principal पूछते हैं: "हर class में कितने students, और हर class का average क्या?" आप पहले sheets की class-wise ढेरियां (piles) बनाएंगे, फिर हर ढेरी को गिनेंगे और average निकालेंगे. GROUP BY बिल्कुल वही ढेरी बनाने वाला step है: यह कई rows को हर group की एक row में समेट देता है, ताकि aggregate functions (COUNT, SUM, AVG, MAX, MIN) हर ढेरी के सवालों के जवाब दे सकें.

दिखती हुई ढेरियों के साथ step by step

-- students table
+-------+-------+-------+
| name  | class | marks |
| Aman  | 10    |  92   |
| Priya | 10    |  88   |
| Rahul | 9     |  45   |
| Neha  | 9     |  95   |
| Kabir | 9     |  60   |
+-------+-------+-------+

SELECT class, COUNT(*) AS students, AVG(marks) AS average
FROM students
GROUP BY class;

-- MySQL अंदर क्या करता है:
-- ढेरी "10": (Aman 92)(Priya 88)          -> count 2, avg 90.0
-- ढेरी "9" : (Rahul 45)(Neha 95)(Kabir 60) -> count 3, avg 66.67

+-------+----------+---------+ | class | students | average | | 10 | 2 | 90.00 | | 9 | 3 | 66.67 | +-------+----------+---------+

पांच rows दो बन गईं — हर ढेरी की एक. यही समेटना पूरा concept है.

Golden rule (सबसे common error की जड़)

SELECT का हर column या तो GROUP BY में हो या किसी aggregate function के अंदर. क्यों? क्योंकि हर output row पूरी ढेरी को represent करती है — name मांगना यानी पूछना "class-9 की ढेरी का KAUN-SA एक name?" वहां तीन names हैं; MySQL ईमानदारी से चुन ही नहीं सकता.

SELECT class, name, AVG(marks)      -- name न grouped है न aggregated
FROM students GROUP BY class;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'name'...

Real काम में सबसे पहले यही error मिलेगा — अब आप जानते हैं यह क्यों है.

Practical patterns जो असल में use होंगे

-- 1. हर class की collected fee
SELECT class, SUM(amount) AS collected
FROM fees GROUP BY class;

-- 2. हर class के topper के marks
SELECT class, MAX(marks) AS topper_marks
FROM students GROUP BY class;

-- 3. DO columns से group: हर class+section combo की एक ढेरी
SELECT class, section, COUNT(*) AS strength
FROM students GROUP BY class, section;

-- 4. सिर्फ condition पूरी करने वाले groups -> HAVING
SELECT class, AVG(marks) AS avg_marks
FROM students
GROUP BY class
HAVING AVG(marks) > 70;

-- 5. Duplicate mobile numbers ढूंढना (classic real-world query!)
SELECT mobile, COUNT(*) AS times
FROM students
GROUP BY mobile
HAVING COUNT(*) > 1;

Pattern 5 पर रुकिए: GROUP BY + HAVING COUNT(*) > 1 किसी भी table में duplicates ढूंढने की standard technique है — interviews में पूछी जाती है और हर real project में चाहिए.

Pipeline में GROUP BY की जगह

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
        (rows      (ढेरियां     (ढेरियां
         छांटो)     बनाओ)       छांटो)

WHERE ढेरी बनने से पहले sheets छांटता है; HAVING बनने के बाद पूरी ढेरियां हटाता है. यह pipeline दिमाग में रही तो आधे GROUP BY questions अपने आप solve.

Frequently Asked Questions

What does GROUP BY do in SQL?

GROUP BY folds rows sharing the same value into one row per group, so aggregate functions like COUNT, SUM and AVG can compute per-group answers such as students per class.

Why does "not in GROUP BY clause" error occur?

Because a selected column is neither grouped nor aggregated — each output row is a whole group, so MySQL cannot honestly pick one value of that column for the group.

How to find duplicate values using GROUP BY?

Group by the column and keep groups larger than one: SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1.