Aggregations — COUNT, SUM, AVG, GROUP BY, HAVING

এক কোয়েরিতে লাখ-লাখ row সংক্ষেপ করা — Aggregation

Read: ~35 min Intermediate 18 practice problems Live SQLite runner

1. From "Show Me Rows" to "Tell Me a Number"

So far every query you wrote returned rows — students, products, orders. But businesses rarely ask "show me every row" — they ask "how many?", "what is the total?", "who has the highest?". Those are aggregate questions, and SQL has a small, beautifully composable set of tools to answer them.

একটি ব্যবসায়িক রিপোর্টে আপনি সাধারণত প্রতিটি row দেখতে চান না; চান সারাংশ — আজ কতগুলো অর্ডার এসেছে, মোট বিক্রি কত, কোন বিভাগে গড় CGPA সবচেয়ে বেশি ইত্যাদি। এই ধরনের প্রশ্নের উত্তর দিতে SQL-এ আছে aggregate function এবং GROUP BY।

In this module you will learn the five core aggregates (COUNT, SUM, AVG, MIN, MAX), how GROUP BY turns one big result into many small per-group results, the difference between WHERE and HAVING, how aggregates handle NULL, and a quick look at ROLLUP and grouping sets.

2. The Five Core Aggregates

An aggregate function takes many rows of input and returns one row of output. It "folds" a column of values into a single number.

Function	What it returns	NULL behaviour
`COUNT(*)`	Total number of rows.	Counts NULL rows too.
`COUNT(col)`	Number of non-NULL values in `col`.	Skips NULLs.
`COUNT(DISTINCT col)`	Number of distinct non-NULL values.	Skips NULLs.
`SUM(col)`	Total of all non-NULL numeric values.	Skips NULLs. Returns NULL if all are NULL.
`AVG(col)`	Arithmetic mean of non-NULL values.	Skips NULLs in numerator and denominator.
`MIN(col)` / `MAX(col)`	Smallest / largest non-NULL value.	Skips NULLs.

মূল কথা: COUNT(*) ছাড়া বাকি সব aggregate function NULL মান উপেক্ষা করে। অর্থাৎ AVG(salary) বের করার সময় NULL salary-গুলো গণনায়ই আসে না। এই আচরণ বুঝতে না পারলে রিপোর্ট ভুল হয়।

aggregates.sql

-- Five aggregates at once over the student table
SELECT
    COUNT(*)            AS total_rows,
    COUNT(cgpa)         AS cgpa_known,
    COUNT(DISTINCT dept) AS num_depts,
    SUM(cgpa)           AS total_cgpa,
    ROUND(AVG(cgpa), 3)  AS avg_cgpa,
    MIN(cgpa)           AS min_cgpa,
    MAX(cgpa)           AS max_cgpa
FROM student;

Note — COUNT(*) vs COUNT(col) COUNT(*) = "how many rows are there?". COUNT(cgpa) = "how many rows have a non-NULL cgpa?". For Rakib (cgpa = NULL), COUNT(*) still includes him; COUNT(cgpa) does not.

3. `GROUP BY` — One Aggregate Per Group

A bare aggregate folds the whole table into one row. GROUP BY instead splits the table into buckets and folds each bucket independently.

GROUP BY dept মানে — প্রথমে student তালিকা বিভাগ অনুযায়ী ভাগ করো (CSE, EEE, BBA), তারপর প্রতিটি বিভাগের জন্য আলাদা করে aggregate চালাও। ফলাফলে প্রতিটি বিভাগের জন্য একটি row পাওয়া যাবে।

group_by_dept.sql

SELECT
    dept,
    COUNT(*)                AS n_students,
    ROUND(AVG(cgpa), 2)    AS avg_cgpa,
    MAX(cgpa)               AS top_cgpa
FROM     student
GROUP BY dept
ORDER BY avg_cgpa DESC;

Selection rule Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate. Otherwise the database has no idea which value of that column to show — there are many rows per group, only one slot in the result.

SELECT dept, name, COUNT(*) FROM student GROUP BY dept; — অর্থহীন: প্রতিটি বিভাগে অনেক name আছে, কিন্তু কেবল একটি দেখানোর জায়গা। অধিকাংশ DBMS এটি error হিসেবে rejected করে।

4. Grouping by Multiple Columns

You can group by more than one column. The buckets become each combination of those columns.

group_by_two_cols.sql

-- Sales summary per (city, category)
SELECT city, category,
       COUNT(*)        AS orders_n,
       SUM(amount)    AS revenue
FROM     orders
GROUP BY city, category
ORDER BY city, revenue DESC;

The result has one row for every actually-occurring (city, category) combination — not the full Cartesian product. Empty buckets simply do not show up.

5. `WHERE` vs `HAVING` — Filter Before vs After

WHERE filters rows before aggregation. HAVING filters groups after aggregation. Mixing them up is the most common aggregation bug in the world.

একটি সহজ মনে রাখার নিয়ম — WHERE চলে GROUP BY-এর আগে, HAVING চলে পরে। তাই aggregate (যেমন COUNT(*)) WHERE-এ লেখা যাবে না; aggregate-এর শর্ত HAVING-এই লিখতে হবে।

where_vs_having.sql

-- "Cities where total revenue from orders >= 1000 BDT exceeds 60,000."
SELECT city,
       COUNT(*)     AS big_orders,
       SUM(amount) AS revenue
FROM     orders
WHERE    amount >= 1000     -- row-level filter, before grouping
GROUP BY city
HAVING   SUM(amount) > 60000 -- group-level filter, after aggregation
ORDER BY revenue DESC;

Stage	Clause	Sees
1. Source	`FROM`	Raw rows.
2. Row filter	`WHERE`	One row at a time. Cannot use aggregates.
3. Bucketize	`GROUP BY`	Splits the surviving rows into groups.
4. Group filter	`HAVING`	Whole groups. Aggregates allowed.
5. Project	`SELECT`	One row per surviving group.
6. Sort/limit	`ORDER BY / LIMIT`	The final shape.

6. NULLs in Aggregates — The Subtle Trap

Recall the rule from the previous module: NULL means unknown. In aggregates, unknown values are simply skipped (with one famous exception: COUNT(*)).

null_avg.sql

-- 5 rows in survey, but only 2 scores are not NULL.
-- AVG(score) = (80 + 60) / 2 = 70 — NOT (80+60+0+0+0)/5 = 28.
SELECT
    COUNT(*)                       AS rows_total,
    COUNT(score)                   AS rows_with_score,
    SUM(score)                     AS total_score,
    AVG(score)                     AS avg_score,
    AVG(COALESCE(score, 0))         AS avg_treating_null_as_0
FROM survey;

Real-world bug Reporting "average customer rating" with AVG(rating) looks fine — until you realize that customers who didn't rate the product are simply absent from the average. If most customers don't rate, AVG(rating) reports only the loud minority.

যাঁরা rating দেননি তাঁদের বাদ দিয়েই গড় বের হচ্ছে। ব্যবসায়িক রিপোর্টে এই point স্পষ্ট না বললে ভুল সিদ্ধান্ত নেওয়া হতে পারে।

7. Subtotals — `ROLLUP`, `CUBE`, `GROUPING SETS`

Standard SQL extends GROUP BY with three modifiers that produce subtotals in a single query — saving you from writing many UNIONs.

GROUP BY ROLLUP(a, b) = group by (a,b), then by (a), then by () — a cumulative roll-up.
GROUP BY CUBE(a, b) = every subset of {a, b} — i.e. all 2² = 4 combinations.
GROUP BY GROUPING SETS ((a,b), (a), ()) = explicit list of groupings.

SQLite-এর বিশেষ ক্ষেত্রে: SQLite এখনও ROLLUP এবং CUBE সরাসরি সাপোর্ট করে না। একই ফলাফল পেতে UNION ALL ব্যবহার করতে হয়। নিচের code block-এ সেই রকম একটি workaround দেখানো হলো।

manual_rollup.sql

-- Manual ROLLUP in SQLite via UNION ALL
SELECT city, category, SUM(amount) AS total
FROM   orders
GROUP BY city, category
UNION ALL
SELECT city, '(subtotal)', SUM(amount)
FROM   orders
GROUP BY city
UNION ALL
SELECT '(grand total)', '', SUM(amount)
FROM   orders
ORDER BY city, category;

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Aggregate function	Function that collapses many input rows into one value.	একাধিক row-কে একটি মান-এ রূপান্তরকারী ফাংশন।
GROUP BY	Splits the input into buckets so each aggregate runs per bucket.	ইনপুটকে গ্রুপে ভাগ করে যাতে aggregate প্রতিটি গ্রুপে আলাদাভাবে চলে।
HAVING	Filter applied after aggregation, on whole groups.	Aggregation-এর পরে গ্রুপের উপর প্রয়োগ করা filter।
WHERE	Filter applied before aggregation, on individual rows.	Aggregation-এর আগে প্রতিটি row-র উপর filter।
NULL	"Unknown" — most aggregates skip NULL values.	অজানা মান — বেশিরভাগ aggregate এটি উপেক্ষা করে।
ROLLUP / CUBE	Standard-SQL extensions for subtotals.	Subtotal পাওয়ার জন্য SQL-এর বর্ধিত GROUP BY।

9. Practice Problems

Use the orders(id, customer, city, category, amount) table from §4 unless a problem says otherwise. Click Show Answer after attempting each one.

প্রতিটি প্রশ্ন আগে নিজে চেষ্টা করুন; তারপর উত্তর মিলিয়ে নিন। উত্তরের কোড সরাসরি ব্রাউজারেই রান করতে পারবেন।

How many orders are there in total?

মোট কতগুলো অর্ডার আছে?
✨ Show Answer (উত্তর দেখুন)
ans1.sql
```
SELECT COUNT(*) AS total_orders FROM orders;
```
How many distinct customers placed orders?

কতজন আলাদা customer অর্ডার দিয়েছেন?
✨ Show Answer
ans2.sql
```
SELECT COUNT(DISTINCT customer) AS distinct_customers
FROM   orders;
```
For each city, show the order count and the total revenue, sorted by revenue desc.

প্রতিটি city-এর জন্য order সংখ্যা ও মোট revenue দেখাও, revenue অনুযায়ী desc।
✨ Show Answer
ans3.sql
```
SELECT city,
       COUNT(*)     AS orders_n,
       SUM(amount) AS revenue
FROM     orders
GROUP BY city
ORDER BY revenue DESC;
```
Find every customer who has spent more than 30,000 BDT in total across all orders.

যাঁরা সব মিলিয়ে 30,000 BDT-র বেশি খরচ করেছেন তাঁদের তালিকা।
✨ Show Answer
ans4.sql
```
SELECT   customer, SUM(amount) AS total_spent
FROM     orders
GROUP BY customer
HAVING   SUM(amount) > 30000
ORDER BY total_spent DESC;
```
Show only those (city, category) buckets that contain at least 2 orders.

কেবল সেই (city, category) groups দেখাও যেগুলোতে অন্তত 2টি অর্ডার আছে।
✨ Show Answer
ans5.sql
```
SELECT   city, category, COUNT(*) AS n
FROM     orders
GROUP BY city, category
HAVING   COUNT(*) >= 2
ORDER BY n DESC;
```
In one sentence, explain why SELECT city, name, COUNT(*) FROM orders GROUP BY city; is wrong.

এক বাক্যে বলুন — উপরের কোয়েরি কেন ভুল?

✨ Show Answer

Answer: name is neither in GROUP BY nor wrapped in an aggregate, so each city group has many possible name values but only one slot to display — the database has no defined choice and rejects the query (or, in MySQL's loose mode, returns an arbitrary one).

name column-টি না GROUP BY-তে আছে, না কোনো aggregate-এর ভেতরে — তাই কোন value দেখাবে সেটি অস্পষ্ট।
Why might AVG(rating) over a product table be misleading?

AVG(rating) কেন বিভ্রান্তিকর হতে পারে?

✨ Show Answer

Answer: Customers who never rated the product have rating = NULL, and AVG silently skips them. So the average reflects only those who rated — usually the very satisfied or very angry minority. Always report COUNT(rating) alongside AVG(rating).

যাঁরা rating দেননি তাঁরা গড়ে আসেননি; তাই গড়টি সাধারণত সবচেয়ে সন্তুষ্ট বা সবচেয়ে অসন্তুষ্ট সংখ্যালঘুর মতামত প্রতিফলিত করে।

Summary — Module 15

Five aggregates (COUNT, SUM, AVG, MIN, MAX) collapse rows into numbers. GROUP BY turns one such collapse into per-bucket collapses. WHERE filters rows before aggregation; HAVING filters groups after. NULLs are silently skipped by every aggregate except COUNT(*) — so always report COUNT(col) next to AVG(col) if NULLs are possible.

Aggregation = অনেক row → এক মান। GROUP BY = বিভিন্ন গ্রুপের জন্য আলাদা আলাদা aggregate। WHERE চলে আগে, HAVING চলে পরে — এই পার্থক্য মনে রাখাই Module 15-এর সবচেয়ে গুরুত্বপূর্ণ শিক্ষা।

Next Module → JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS — multiple tables in one query.

Prev: Operators Next: JOINs

1. From "Show Me Rows" to "Tell Me a Number"

2. The Five Core Aggregates

3. GROUP BY — One Aggregate Per Group

4. Grouping by Multiple Columns

5. WHERE vs HAVING — Filter Before vs After

6. NULLs in Aggregates — The Subtle Trap

7. Subtotals — ROLLUP, CUBE, GROUPING SETS

8. Glossary (শব্দকোষ)

9. Practice Problems

Summary — Module 15

3. `GROUP BY` — One Aggregate Per Group

5. `WHERE` vs `HAVING` — Filter Before vs After

7. Subtotals — `ROLLUP`, `CUBE`, `GROUPING SETS`