Aggregation — GROUP BY আর HAVING

১ · অনেক সারি থেকে একটি উত্তর — aggregate ফাংশন

পর্ব ১৫-এ SELECT দিয়ে সারি বাছাই, ফিল্টার আর সাজানো শিখেছেন। কিন্তু সেখানে প্রতিটি ফল-সারি ছিল টেবিলের একটি সারির প্রতিচ্ছবি। বাস্তবের বেশিরভাগ প্রশ্ন এমন নয়: “এ মাসে কয়টি অর্ডার এসেছে?”, “মোট বিক্রি কত টাকা?”, “CSE বিভাগের গড় CGPA কত?” — এদের উত্তর একটি সংখ্যা, যা অনেক সারি মিলিয়ে বের হয়। এই কাজটির নাম aggregation (একত্রীকরণ), আর যে ফাংশন অনেক সারি নিয়ে একটি মান দেয়, তাকে বলে aggregate function.

SQL standard-এর পাঁচটি মূল aggregate ফাংশন প্রায় সব DBMS-এ একই নামে আছে:

টেবিলের তৃতীয় কলামটিই এই পর্বের প্রথম শিক্ষা। পর্ব ১৬-এ দেখেছেন NULL মানে “অজানা”, শূন্য নয়। অজানা মান যোগ করলে কী হয়? 3.75 + NULL একটি সাধারণ হিসাবে NULL — কিন্তু aggregate ফাংশন সেভাবে চলে না। SQL standard-এর নিয়ম: aggregate ফাংশন হিসাবের আগে NULL মানগুলো ফেলে দেয়, তারপর বাকিদের ওপর কাজ করে। একমাত্র ব্যতিক্রম COUNT(*), যা কোনো কলামের মান দেখেই না — শুধু সারি গোনে।

পরীক্ষা করে দেখি। db-uni ডেটাবেসের students টেবিলে আটজন শিক্ষার্থী: কলামগুলো student_id, name, dept_id (বিভাগ — 1 = CSE, 2 = EEE, 3 = BBA), batch আর cgpa. এর মধ্যে Fahim Karim-এর cgpa NULL (এখনো হিসাব হয়নি), আর Habib Ullah-র dept_id NULL (বিভাগ বাছেননি)। পুরো ডেটা এডিটরের নিচের “নমুনা ডেটা” বাক্সে।

SELECT COUNT(*)                AS all_rows,
       COUNT(cgpa)             AS with_cgpa,
       COUNT(DISTINCT dept_id) AS depts,
       SUM(cgpa) AS total, AVG(cgpa) AS average,
       MIN(cgpa) AS lowest, MAX(cgpa) AS highest
FROM students;

-- গড় নিজে হাতে: কোন সংখ্যা দিয়ে ভাগ করবেন?
SELECT SUM(cgpa) / COUNT(*)    AS divided_by_8,
       SUM(cgpa) / COUNT(cgpa) AS divided_by_7
FROM students;

দুটি ছোট কিন্তু কাজের তথ্য। প্রথমত, COUNT(1) আর COUNT(*) একই কাজ করে — 1 কখনো NULL নয়, তাই প্রতিটি সারি গোনা হয়; “COUNT(1) দ্রুত” কথাটি আধুনিক DBMS-এ একটি পুরোনো গুজব। দ্বিতীয়ত, SUM(DISTINCT col) আর AVG(DISTINCT col)-ও লেখা যায়, কিন্তু খুব কমই কাজে লাগে: দুটি অর্ডারের দাম কাকতালীয়ভাবে সমান হলে একটি বাদ পড়ে যাবে — এটি প্রায় সবসময় ভুল উত্তর।

২ · ফাঁকা সেটের ফাঁদ — SUM কেন NULL, আর COUNT কেন 0

এবার একটি প্রশ্ন যা দেখতে নিরীহ: “যে গ্রাহক কিছুই কেনেননি, তিনি মোট কত টাকা খরচ করেছেন?” উত্তর তো শূন্য টাকা। কিন্তু SQL বলে NULL. দোকানের ডেটাবেস db-shop দিয়ে দেখি। এতে পাঁচটি টেবিল: customers (customer_id, name, city, phone, joined_on — ৮ জন গ্রাহক), categories (category_id, name — ৪টি শ্রেণি), products (product_id, name, category_id, price, stock — ১০টি পণ্য), orders (order_id, customer_id, order_date, status — ১০টি অর্ডার, status হলো pending, shipped, delivered বা cancelled) আর order_items (order_id, product_id, quantity, unit_price — ১৬টি লাইন; unit_price হলো বিক্রির দিনের দাম)। গ্রাহক Tanvir Ahmed (customer_id 5) একটিও অর্ডার দেননি।

-- Tanvir (5)-এর অর্ডার: কোনো সারি মেলে না
SELECT COUNT(*) AS orders, MAX(order_date) AS last_order
FROM orders WHERE customer_id = 5;

-- তাঁর খরচ: SUM ফাঁকা সেটে NULL, COALESCE বদলে দেয় 0-তে
SELECT SUM(quantity * unit_price)              AS spent,
       COALESCE(SUM(quantity * unit_price), 0) AS spent_safe
FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 5);

-- GROUP BY থাকলে: ফাঁকা সেট থেকে কোনো দলই হয় না
SELECT customer_id, COUNT(*) FROM orders
WHERE customer_id = 5 GROUP BY customer_id;

কেন এমন নকশা? একটিও সংখ্যা না থাকলে যোগফল শূন্য — গণিতে এটাই স্বাভাবিক। কিন্তু MAX বা AVG-এর ক্ষেত্রে নয়: একটিও সংখ্যা না থাকলে গড় হয় ০ ÷ ০, যার কোনো মান নেই। সবার জন্য একটি নিয়ম রাখতে standard বলেছে: সারি না থাকলে COUNT ছাড়া সবাই NULL. এই সিদ্ধান্ত নিয়ে চার দশক ধরে তর্ক চলছে (ভাবনার প্রশ্ন ১), কিন্তু বাস্তবে নিয়মটি মেনে চলতে হয়। কাজের কৌশল তিনটি:

• COALESCE(SUM(x), 0) — COALESCE তালিকার প্রথম অ-NULL মান ফেরত দেয়। রিপোর্টে “০ টাকা” দেখাতে এটিই সবচেয়ে বহনযোগ্য (portable) উপায়।
• SQLite-এর TOTAL(x) — SUM-এর মতো, কিন্তু ফাঁকা সেটে 0.0 দেয় আর সবসময় দশমিক সংখ্যা ফেরত দেয়। শুধু SQLite-এ আছে, তাই বহনযোগ্য কোডে না লেখাই ভালো।
• অ্যাপের কোডে NULL ধরে রাখা — Python-এ None, Java-তে null আসবে। total + 100 লিখলে ক্র্যাশ বা ভুল — তাই হয় SQL-এ COALESCE, নয়তো অ্যাপে স্পষ্ট পরীক্ষা।

৩ · GROUP BY — সারিগুলো ঝুড়িতে ভাগ, প্রতি ঝুড়িতে একটি উত্তর

এখন পর্যন্ত aggregate পুরো টেবিলকে একটি দল ধরে একটি সারি দিয়েছে। দোকানের মালিকের পরের প্রশ্ন: “প্রতিটি অবস্থার (status) কয়টি অর্ডার?” এখানে চারটি উত্তর চাই, প্রতিটি status-এর জন্য একটি। GROUP BY ঠিক এই কাজ করে: যে কলাম বা কলামগুলো দেওয়া হয়, তাদের মান যেসব সারিতে এক, সেগুলো একটি দলে (group) যায়; তারপর প্রতিটি দলে আলাদা করে aggregate হিসাব হয়, আর প্রতিটি দল থেকে ঠিক একটি ফল-সারি আসে। নিচের ডায়াগ্রামে দশটি অর্ডারের ওপর GROUP BY status ধাপে ধাপে দেখুন:

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

 order_id │ customer_id │ status
 1001     │ 1           │ delivered
 1002     │ 2           │ delivered
 1003     │ 1           │ shipped
 1004     │ 3           │ cancelled
 1005     │ 4           │ delivered
 1006     │ 6           │ pending
 1007     │ 2           │ delivered
 1008     │ 8           │ shipped
 1009     │ 1           │ pending
 1010     │ 7           │ delivered

[delivered]  1001  1002  1005  1007  1010
[shipped]    1003  1008
[pending]    1006  1009
[cancelled]  1004

customer_id আর order_date সারিগুলোর সাথেই
ঝুড়িতে আছে, কিন্তু ফলে আসবে না

[delivered]  1001  1002  1005  1007  1010   → COUNT = 5
[shipped]    1003  1008                     → COUNT = 2
[pending]    1006  1009                     → COUNT = 2
[cancelled]  1004                           → COUNT = 1

 status    │ orders
 delivered │ 5
 shipped   │ 2
 pending   │ 2
 cancelled │ 1

এখনো ক্রম নিশ্চিত নয় — DBMS যে ক্রমে
দল বানিয়েছে, সেই ক্রমেই আসতে পারে

 status    │ orders
 delivered │ 5
 pending   │ 2
 shipped   │ 2
 cancelled │ 1

pending আর shipped দুটিতেই 2 —
দ্বিতীয় key (status) টাই ভাঙে

এবার নিজে চালান। দ্বিতীয় কোয়েরিতে দুটি কলাম দিয়ে দল: (customer_id, status) — দুটির মানই যেসব সারিতে এক, তারা এক দলে।

-- প্রতিটি status-এ কয়টি অর্ডার
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;

-- একাধিক কলামে দল: প্রতিজন গ্রাহকের প্রতিটি status-এ কয়টি
SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

বিশ্ববিদ্যালয়ের দিকে একই প্রশ্ন: প্রতিটি বিভাগে কতজন শিক্ষার্থী আর তাঁদের গড় CGPA কত? এখানে দেখবেন NULL নিজেও একটি দল হয়।

SELECT dept_id,
       COUNT(*)             AS students,
       COUNT(cgpa)          AS with_cgpa,
       ROUND(AVG(cgpa), 2)  AS avg_cgpa,
       MAX(cgpa)            AS best
FROM students
GROUP BY dept_id
ORDER BY dept_id;

-- সব শিক্ষার্থী মিলিয়ে গড় (অংশ ৯-এ কাজে লাগবে)
SELECT ROUND(AVG(cgpa), 2) AS overall FROM students;

৪ · SELECT-এর লৌহ নিয়ম — প্রতিটি কলাম GROUP BY-তে, নয়তো aggregate-এ

অংশ ৩-এর ডায়াগ্রামের দ্বিতীয় ধাপ মনে করুন: delivered ঝুড়িতে পাঁচটি সারি, পাঁচজন আলাদা গ্রাহক। এখন কেউ যদি লেখেন SELECT status, customer_id, COUNT(*) … GROUP BY status, তাহলে delivered-এর একমাত্র ফল-সারিতে customer_id কোনটি বসবে — 1, 2, 4, 2 না 7? প্রশ্নটির কোনো অর্থপূর্ণ উত্তর নেই। তাই SQL standard-এর নিয়ম:

কিন্তু সব DBMS নিয়মটি পাহারা দেয় না। SQLite এমন কোয়েরি চুপচাপ চালিয়ে দেয় — দলের বাইরের এমন কলামকে বলে bare column (“খালি কলাম”)। নিচে প্রতিটি বিভাগের সেরা শিক্ষার্থীর নাম বের করার একটি “শর্টকাট” আর তার বিপদ দেখুন:

-- name GROUP BY-তে নেই, aggregate-এও নেই — তবু SQLite চালায়
SELECT dept_id, name, MAX(cgpa) AS best
FROM students GROUP BY dept_id;

-- একই কৌশল AVG দিয়ে: name এখন কার?
SELECT dept_id, name, ROUND(AVG(cgpa), 2) AS avg_cgpa
FROM students GROUP BY dept_id;

PostgreSQL একই প্রথম কোয়েরি চালাতেই দেয় না:

SELECT dept_id, name, MAX(cgpa) AS best
FROM students GROUP BY dept_id;

-- ERROR:  column "students.name" must appear in the GROUP BY clause
--         or be used in an aggregate function
-- LINE 1: SELECT dept_id, name, MAX(cgpa) AS best

MySQL-এর গল্পটি ইতিহাসের। পুরোনো MySQL (5.6 পর্যন্ত, ডিফল্ট সেটিংসে) SQLite-এর মতোই bare column মেনে নিত, আর অনেক বছর ধরে লাখো ওয়েবসাইটের কোড এর ওপর দাঁড়িয়ে ছিল। MySQL 5.7.5 থেকে ONLY_FULL_GROUP_BY নামের SQL mode ডিফল্টে চালু, যা প্রায় PostgreSQL-এর মতো ত্রুটি দেয়: ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column … which is not functionally dependent on columns in GROUP BY clause. পুরোনো প্রজেক্টে কেউ কেউ এই mode বন্ধ করে দেন — তাতে ত্রুটি চলে যায়, ভুলটি থেকে যায়।

তাহলে “প্রতিটি বিভাগের সেরা শিক্ষার্থীর নাম” সঠিকভাবে কীভাবে? তিনটি পরিষ্কার পথ:

• নামটি সত্যিই একই হলে GROUP BY-তে যোগ করুন — যেমন GROUP BY s.dept_id, d.name, যেখানে বিভাগের নাম প্রতিটি dept_id-এর জন্য একটিই। GROUP BY-তে primary key থাকলে PostgreSQL (9.1 থেকে) আর MySQL (5.7 থেকে) বাকি কলাম নিজেই মেনে নেয়, কারণ তারা functional dependency চেনে।
• “যেকোনো একটি হলেই চলবে” হলে স্পষ্ট বলুন — MySQL-এ ANY_VALUE(name), PostgreSQL 16 থেকে any_value(name) (SQL:2023 standard-এ যোগ হয়েছে), অথবা সব DBMS-এ MIN(name).
• “সর্বোচ্চ মানের সারিটি” চাইলে — এটি আসলে “প্রতি দলে top-1” সমস্যা: subquery (পর্ব ২১) বা ROW_NUMBER() window function (পর্ব ২৬) দিয়ে, যা টাই-ও ঠিকভাবে সামলায় — বিস্তারিত পর্ব ২৭-এ।

৫ · WHERE বনাম HAVING — ভাগের আগে ছাঁকা, ভাগের পরে ছাঁকা

মালিক এবার জানতে চান: “বাতিল অর্ডার বাদ দিয়ে, কোন কোন অর্ডারের মোট দাম ৫,০০০ টাকার বেশি?” এখানে দুটি শর্ত, আর দুটির চরিত্র আলাদা। “বাতিল নয়” একটি সারির শর্ত — প্রতিটি লাইন দেখেই বলা যায়। “মোট দাম ৫,০০০-এর বেশি” একটি দলের শর্ত — দলের সব লাইন যোগ না করে বলা যায় না। প্রথমটি যায় WHERE-এ, দ্বিতীয়টি HAVING-এ। কেন, তা বুঝতে SELECT-এর যৌক্তিক ক্রম জানতে হবে — আপনি যে ক্রমে লেখেন (SELECT আগে), DBMS সেই ক্রমে ভাবে না:

order │ পণ্য │ qty × দাম      = লাইন
1001  │ 101  │ 1 × 15000      = 15000
1001  │ 102  │ 2 × 1200       =  2400
1002  │ 104  │ 1 × 650        =   650
1002  │ 105  │ 2 × 550        =  1100
1003  │ 108  │ 3 × 420        =  1260
1003  │ 109  │ 2 × 260        =   520
1004  │ 107  │ 1 × 4500       =  4500
1005  │ 106  │ 2 × 2200       =  4400
1005  │ 104  │ 1 × 650        =   650
1006  │ 110  │ 1 × 3500       =  3500
1007  │ 102  │ 1 × 1150       =  1150
1007  │ 108  │ 5 × 420        =  2100
1008  │ 105  │ 1 × 550        =   550
1009  │ 101  │ 1 × 14500      = 14500
1009  │ 110  │ 1 × 3500       =  3500
1010  │ 109  │ 4 × 260        =  1040

order │ পণ্য │ qty × দাম      = লাইন
1001  │ 101  │ 1 × 15000      = 15000
1001  │ 102  │ 2 × 1200       =  2400
1002  │ 104  │ 1 × 650        =   650
1002  │ 105  │ 2 × 550        =  1100
1003  │ 108  │ 3 × 420        =  1260
1003  │ 109  │ 2 × 260        =   520
1004  │ 107  │ 1 × 4500       ✗ cancelled
1005  │ 106  │ 2 × 2200       =  4400
1005  │ 104  │ 1 × 650        =   650
1006  │ 110  │ 1 × 3500       =  3500
1007  │ 102  │ 1 × 1150       =  1150
1007  │ 108  │ 5 × 420        =  2100
1008  │ 105  │ 1 × 550        =   550
1009  │ 101  │ 1 × 14500      = 14500
1009  │ 110  │ 1 × 3500       =  3500
1010  │ 109  │ 4 × 260        =  1040

[1001]  15000 + 2400
[1002]  650 + 1100
[1003]  1260 + 520
[1005]  4400 + 650
[1006]  3500
[1007]  1150 + 2100
[1008]  550
[1009]  14500 + 3500
[1010]  1040

[1001]  15000 + 2400   = 17400
[1002]  650 + 1100     = 1750
[1003]  1260 + 520     = 1780
[1005]  4400 + 650     = 5050
[1006]  3500           = 3500
[1007]  1150 + 2100    = 3250
[1008]  550            = 550
[1009]  14500 + 3500   = 18000
[1010]  1040           = 1040

[1001]  17400   ✓
[1002]   1750
[1003]   1780
[1005]   5050   ✓
[1006]   3500
[1007]   3250
[1008]    550
[1009]  18000   ✓
[1010]   1040

 order_id │ total
 1001     │ 17400
 1005     │ 5050
 1009     │ 18000

alias “total” এই ধাপে জন্মায় —
তাই WHERE তাকে চেনে না

 order_id │ total
 1009     │ 18000
 1001     │ 17400
 1005     │ 5050

SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
WHERE order_id NOT IN
      (SELECT order_id FROM orders WHERE status = 'cancelled')
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 5000
ORDER BY total DESC;

-- ভুল জায়গায় aggregate: WHERE-এ SUM
SELECT order_id FROM order_items
WHERE SUM(quantity * unit_price) > 5000 GROUP BY order_id;

এখানে একটি বহনযোগ্যতার কথা: HAVING-এ আমরা পুরো SUM(quantity * unit_price) আবার লিখেছি, alias total নয়। SQLite আর MySQL HAVING-এ SELECT-এর alias মেনে নেয়, কিন্তু PostgreSQL আর SQL Server নেয় না (column "total" does not exist) — standard-এর যৌক্তিক ক্রমে HAVING চলে SELECT-এর আগে। পুনরাবৃত্তিটি দেখতে খারাপ, কিন্তু সব জায়গায় চলে; DBMS একই হিসাব দুবার করে না।

দুটি টেবিল মিলিয়ে: বেশি খরচের গ্রাহক

“কোন গ্রাহকেরা বাতিল বাদে মোট ৫,০০০ টাকার বেশি খরচ করেছেন?” — এখানে গ্রাহক থাকে orders-এ, টাকা থাকে order_items-এ, তাই দুটিকে মেলাতে হয়। নিচের JOIN অংশটি শুধু বলে: “orders-এর প্রতিটি সারির পাশে একই order_id-এর order_items লাইনগুলো বসাও”। JOIN-এর পুরো গল্প পরের পর্বে (পর্ব ১৯); এখানে মূল বিষয় GROUP BY আর HAVING.

SELECT o.customer_id,
       COUNT(DISTINCT o.order_id)        AS orders,
       COUNT(*)                          AS lines,
       SUM(oi.quantity * oi.unit_price)  AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 5000
ORDER BY spent DESC;

কোনটি কোথায় — আর পারফরম্যান্স

যে শর্ত aggregate ছাড়াই লেখা যায়, সেটি HAVING-এ লিখলেও প্রায়ই একই ফল আসে — যেমন HAVING order_id <> 1004. কিন্তু তখন DBMS আগে বাতিল অর্ডারের লাইনগুলোও পড়ে, দলে ভাগ করে, যোগ করে, আর শেষে দলটি ফেলে দেয় — অকারণ কাজ। WHERE-এ লিখলে সারিগুলো শুরুতেই বাদ যায়, আর সেই কলামে index থাকলে সেগুলো পড়াই লাগে না। দশটি অর্ডারে পার্থক্য চোখে পড়বে না; এক কোটি লাইনের bKash-ধাঁচের লেনদেন টেবিলে “শুধু গত সপ্তাহ” শর্তটি WHERE-এ থাকা আর HAVING-এ থাকার পার্থক্য সেকেন্ড বনাম মিনিট হতে পারে। PostgreSQL-এর optimizer aggregate-বিহীন HAVING শর্ত নিজেই WHERE-এ সরিয়ে নিতে পারে, কিন্তু সব DBMS-এ সব ক্ষেত্রে তা নিশ্চিত নয়। নিয়মটি তাই সহজ:

৬ · হিসাব করা মান দিয়ে দল — মাস, দামের সীমা আর দল সাজানো

GROUP BY শুধু কলামের নাম নেয় না, যেকোনো expression নিতে পারে। এতে খুব কাজের দুটি রিপোর্ট সহজ হয়: সময় অনুযায়ী (মাসে মাসে বিক্রি) আর পরিসর অনুযায়ী (দামের সীমা, বয়সের ধাপ, CGPA-র শ্রেণি)। পর্ব ১৭-এর তারিখ ফাংশন strftime('%Y-%m', order_date) একটি তারিখ থেকে শুধু বছর-মাস বের করে ('2025-09'), আর পর্ব ১৬-এর CASE একটি দাম দেখে তার “সীমা”র নাম দেয়:

-- মাসে মাসে অর্ডারের সংখ্যা
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

-- দামের সীমা অনুযায়ী পণ্য
SELECT CASE WHEN price < 1000 THEN '1: below 1000'
            WHEN price < 5000 THEN '2: 1000-4999'
            ELSE '3: 5000+' END AS band,
       COUNT(*) AS products, MIN(price) AS low, MAX(price) AS high
FROM products
GROUP BY band
ORDER BY band;

এখানে GROUP BY month আর GROUP BY band — SELECT-এর alias দিয়ে দল। SQLite, MySQL আর PostgreSQL এটি মেনে নেয়, কিন্তু SQL Server আর (23ai সংস্করণের আগের) Oracle নেয় না; সেখানে পুরো expression আবার লিখতে হয়: GROUP BY strftime(…)-এর সমতুল্য। একইভাবে GROUP BY 1 (SELECT-এর প্রথম কলাম) SQLite, MySQL আর PostgreSQL-এ চলে, SQL Server-এ চলে না; আর কলামের ক্রম বদলালে নিঃশব্দে ভুল দল হয় বলে অনেক দলের কোডিং নিয়মে এটি নিষেধ।

-- PostgreSQL-এ order_date আসল DATE টাইপ হলে
SELECT date_trunc('month', order_date)::date AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;
-- MySQL:      GROUP BY DATE_FORMAT(order_date, '%Y-%m')
-- SQL Server: GROUP BY FORMAT(order_date, 'yyyy-MM')  (বা YEAR() আর MONTH() দুটি কলামে)

দল সাজানো আর “কোন মাসে কিছুই হয়নি”

দল সাজাতে ORDER BY-তে group-কলাম, aggregate বা তার alias — তিনটিই চলে: ORDER BY COUNT(*) DESC সবচেয়ে বেশি অর্ডারের মাস আগে দেখায়। “Top 3 শ্রেণি” চাইলে শেষে LIMIT 3. কিন্তু একটি ফাঁদ মনে রাখুন: GROUP BY শুধু ডেটায় যা আছে তা থেকে দল বানায়। নভেম্বরে একটিও অর্ডার না এলে ফলে “2025-11 | 0” সারিটি আসবে না — সারিটিই থাকবে না। মাসিক চার্টে এটি একটি ফাঁকা জায়গা হয়ে যায়, আর অনেকে ভুল করে ধরে নেন ডেটা হারিয়েছে। সমাধান হলো সব মাসের একটি তালিকা (calendar টেবিল বা recursive CTE, পর্ব ২৫) বানিয়ে তার সাথে LEFT JOIN (পর্ব ১৯) করা — এখানে ঠিক অংশ ২-এর COALESCE আবার কাজে লাগে।

৭ · শর্তসাপেক্ষ গোনা আর দলের ভেতরের তালিকা — FILTER, CASE আর GROUP_CONCAT

অনেক রিপোর্টে একই সারিতে কয়েকটি আলাদা গোনা লাগে: “মোট অর্ডার, তার মধ্যে কয়টি delivered, কয়টি এখনো খোলা, কয়টি বাতিল” — পাশাপাশি চারটি কলাম। চারটি আলাদা কোয়েরি না লিখে একটিতেই করা যায়, এই কৌশলের নাম conditional aggregation. দুটি উপায়: standard-এর FILTER clause (COUNT(*) FILTER (WHERE …)) — PostgreSQL 9.4 আর SQLite 3.30 থেকে আছে; আর সব DBMS-এ চলে এমন পুরোনো কৌশল SUM(CASE WHEN … THEN 1 ELSE 0 END):

SELECT COUNT(*) AS all_orders,
       COUNT(*) FILTER (WHERE status = 'delivered')         AS delivered,
       COUNT(*) FILTER (WHERE status IN ('pending', 'shipped')) AS still_open,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- মাসে মাসে, একই কৌশল
SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) FILTER (WHERE status = 'delivered')         AS delivered,
       SUM(CASE WHEN status <> 'delivered' THEN 1 ELSE 0 END) AS others
FROM orders
GROUP BY month ORDER BY month;

আরও তিনটি সমতুল্য লেখা প্রায়ই দেখবেন: COUNT(CASE WHEN status = 'delivered' THEN 1 END) (ELSE নেই, তাই অমিল সারিতে NULL, আর COUNT তা গোনে না), MySQL আর SQLite-এ SUM(status = 'delivered') (তুলনার ফল 1 বা 0 — সংক্ষিপ্ত, কিন্তু PostgreSQL-এ boolean-এর SUM চলে না), আর FILTER শুধু COUNT নয়, যেকোনো aggregate-এ বসে: SUM(quantity * unit_price) FILTER (WHERE …). MySQL আর SQL Server-এ FILTER নেই — সেখানে CASE-ই ভরসা।

এই কৌশলের আরেক নাম “pivot”: সারির মান (delivered, pending…) কলামের শিরোনাম হয়ে যায়। Excel-এর pivot table এভাবেই কাজ করে; SQL-এ বড় আকারের pivot পর্ব ২৭-এ।

দলের সদস্যদের নাম এক ঘরে — GROUP_CONCAT আর STRING_AGG

কখনো কখনো দলের সংখ্যা নয়, সদস্যদের তালিকা চাই: “প্রতিটি শ্রেণিতে কোন কোন পণ্য?” SQLite আর MySQL-এ এর ফাংশন GROUP_CONCAT, PostgreSQL আর SQL Server-এ STRING_AGG — দুটিই দলের লেখাগুলো একটি বিভাজক (separator) দিয়ে জোড়া লাগায়:

SELECT category_id, COUNT(*) AS n,
       GROUP_CONCAT(name, ', ') AS products
FROM products
GROUP BY category_id;

-- তালিকার ভেতরেও ক্রম (SQLite 3.44+)
SELECT category_id,
       GROUP_CONCAT(name, ' / ' ORDER BY price DESC) AS by_price
FROM products
GROUP BY category_id;

-- MySQL: বিভাজক SEPARATOR দিয়ে; ডিফল্ট ফলের সীমা 1024 byte!
SELECT category_id, GROUP_CONCAT(name ORDER BY price DESC SEPARATOR ', ')
FROM products GROUP BY category_id;

-- PostgreSQL
SELECT category_id, STRING_AGG(name, ', ' ORDER BY price DESC)
FROM products GROUP BY category_id;

-- SQL Server 2017+ (Oracle-এ একই রূপে LISTAGG)
SELECT category_id, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY price DESC)
FROM products GROUP BY category_id;

৮ · উপ-মোট আর সর্বমোট — ROLLUP, CUBE আর GROUPING SETS

হিসাবরক্ষকের রিপোর্টে প্রায়ই একসাথে তিন স্তর লাগে: প্রতিটি মাসের প্রতিটি status-এ কয়টি অর্ডার, প্রতিটি মাসের উপ-মোট (subtotal), আর সব মিলিয়ে সর্বমোট — ঠিক যেমন বার্ষিক হিসাবের খাতায় প্রতি পাতার নিচে পাতার যোগফল আর শেষ পাতায় মোট। একটি সাধারণ GROUP BY একটি স্তরই দেয়। SQL:1999 standard এর জন্য তিনটি সম্প্রসারণ এনেছে: ROLLUP, CUBE আর GROUPING SETS. PostgreSQL (9.5 থেকে), SQL Server আর Oracle তিনটিই সমর্থন করে; MySQL শুধু WITH ROLLUP; আর SQLite কোনোটিই সমর্থন করে না — তাই নিচের কোড পাতায় চালানো যাবে না।

SELECT to_char(order_date, 'YYYY-MM') AS month, status, COUNT(*) AS orders
FROM orders
GROUP BY ROLLUP (to_char(order_date, 'YYYY-MM'), status)
ORDER BY month, status;

 month   │ status    │ orders
 2025-09 │ cancelled │ 1
 2025-09 │ delivered │ 3
 2025-09 │ pending   │ 1
 2025-09 │ shipped   │ 1
 2025-09 │ NULL      │ 6     ← সেপ্টেম্বরের উপ-মোট
 2025-10 │ delivered │ 2
 2025-10 │ pending   │ 1
 2025-10 │ shipped   │ 1
 2025-10 │ NULL      │ 4     ← অক্টোবরের উপ-মোট
 NULL    │ NULL      │ 10    ← সর্বমোট

ROLLUP (a, b) মানে তিনটি দলবদ্ধতা একসাথে: (a, b), (a), আর () — ডান দিক থেকে একটি একটি কলাম “গুটিয়ে” নেওয়া। n কলামে ROLLUP দেয় n + 1টি স্তর। CUBE (a, b) দেয় সব সম্ভাব্য উপসেট: (a, b), (a), (b), () — n কলামে 2ⁿটি স্তর; এখানে অতিরিক্ত চারটি সারি আসত, প্রতিটি status-এর দুই মাস মিলিয়ে মোট (delivered 5, pending 2, shipped 2, cancelled 1). আর GROUPING SETS ((month), (status), ()) দিয়ে নিজে বেছে নেন কোন স্তরগুলো চাই।

উপ-মোটের সারিতে NULL মানে “সব”। কিন্তু ডেটায় আসল NULL থাকলে (যেমন বিভাগহীন Habib) দুটি আলাদা করবেন কীভাবে? এর জন্য GROUPING(status) ফাংশন: উপ-মোটের সারিতে 1, সাধারণ সারিতে 0 — CASE WHEN GROUPING(status) = 1 THEN 'All' ELSE status END লিখে লেবেল বসানো যায়। MySQL-এ লেখার ধরন আলাদা:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, status, COUNT(*) AS orders
FROM orders
GROUP BY month, status WITH ROLLUP;   -- GROUPING() চলে MySQL 8.0 থেকে

SQLite-এ একই ফল পেতে তিনটি স্তর আলাদা তিনটি কোয়েরিতে হিসাব করে UNION ALL দিয়ে জোড়া লাগান (পর্ব ২২-এ বিস্তারিত)। নিচের কোয়েরি অংশ ৩-এর যেকোনো db-shop এডিটরে বসিয়ে চালাতে পারেন — দশটি সারি আসবে, ঠিক উপরের মতো সংখ্যা; lvl কলাম বলে দেয় কোন সারি কোন স্তরের:

SELECT 1 AS lvl, strftime('%Y-%m', order_date) AS month, status, COUNT(*) AS orders
FROM orders GROUP BY strftime('%Y-%m', order_date), status
UNION ALL
SELECT 2, strftime('%Y-%m', order_date), NULL, COUNT(*)
FROM orders GROUP BY strftime('%Y-%m', order_date)
UNION ALL
SELECT 3, NULL, NULL, COUNT(*) FROM orders
ORDER BY lvl, month, status;

খরচের পার্থক্যটি বোঝার মতো: UNION ALL সংস্করণে orders টেবিল তিনবার পড়া হয়। ROLLUP-সমর্থিত DBMS সাধারণত একবার সাজিয়ে সব স্তর একসাথে বের করে — কারণ (month, status)-এর দলগুলো থেকেই (month)-এর উপ-মোট যোগ করে পাওয়া যায়। COUNT আর SUM-এর জন্য এই “ছোট দলের যোগফল থেকে বড় দল” কৌশল কাজ করে; AVG-এর জন্য করে না — কেন, তা পরের অংশে। ডেটা ওয়্যারহাউসের OLAP রিপোর্টে (পর্ব ৫৫) ROLLUP আর CUBE-ই মূল হাতিয়ার।

৯ · গড়ের গড় — সাধারণ গড় বনাম ভারিত গড়ের ফাঁদ

এই অংশের মূল শব্দ ভারিত গড় (weighted average) — যেখানে প্রতিটি মান তার “ওজন” অনুযায়ী গুরুত্ব পায়। অংশ ৩-এর ফল আবার দেখুন: চারটি দলের গড় CGPA 2.8, 3.65, 2.95 আর 3.1; আর সবার গড় 3.35. এখন কেউ রিপোর্টে লিখলেন, “বিভাগগুলোর গড় নিয়ে তার গড় = সবার গড়”। ঠিক কি? নিচে হিসাবটি ধাপে ধাপে:

[CSE  1]  3.75  3.40  3.90  3.55
[EEE  2]  2.95  (Fahim: NULL)
[BBA  3]  3.10
[NULL  ]  2.80          (Habib — বিভাগ নেই)

মোট ৭টি জানা cgpa

[CSE  1]  14.60 ÷ 4 = 3.65   (n = 4)
[EEE  2]   2.95 ÷ 1 = 2.95   (n = 1)
[BBA  3]   3.10 ÷ 1 = 3.10   (n = 1)
[NULL  ]   2.80 ÷ 1 = 2.80   (n = 1)

(3.65 + 2.95 + 3.10 + 2.80) ÷ 4
  = 12.50 ÷ 4
  = 3.125

একজনের BBA আর চারজনের CSE —
দুটিকেই সমান গুরুত্ব দেওয়া হলো

(3.65 × 4 + 2.95 × 1 + 3.10 × 1 + 2.80 × 1) ÷ (4 + 1 + 1 + 1)
  = (14.60 + 2.95 + 3.10 + 2.80) ÷ 7
  = 23.45 ÷ 7
  = 3.35

দল      SUM     COUNT
CSE     14.60   4
EEE      2.95   1
BBA      3.10   1
NULL     2.80   1
------  ------  -----
মোট    23.45   7   → 23.45 ÷ 7 = 3.35

SUM-এর যোগ = মোট SUM, COUNT-এর যোগ = মোট COUNT
AVG-এর গড় ≠ মোট AVG

গাণিতিকভাবে: দল i-এর গড় m_i আর সদস্যসংখ্যা n_i হলে আসল গড় = Σ n_im_i ÷ Σ n_i. সাধারণ গড় Σ m_i ÷ k এর সমান হয় কেবল তখনই, যখন সব n_i সমান — অথবা কাকতালীয়ভাবে। দলের আকার যত অসমান, ভুল তত বড়। বাস্তবে এর সবচেয়ে পরিচিত উদাহরণ আপনার নিজের CGPA: প্রতিটি কোর্সের grade point-এর সাধারণ গড় নয়, credit দিয়ে ভারিত গড় — ৩ credit-এর কোর্স ১.৫ credit-এর ল্যাবের দ্বিগুণ ওজন পায়। SQL-এ তাই লিখতে হয় SUM(credits * point) / SUM(credits), AVG(point) নয় (পর্ব ১১-এ ঠিক এই হিসাব দেখেছেন)।

১০ · AI-এর যুগে aggregation — যে কোয়েরি চলে, কিন্তু ভুল বলে

“মাসিক বিক্রির রিপোর্ট দাও” লিখলে AI কয়েক সেকেন্ডে একটি GROUP BY দেয়, আর সেটি প্রায় সবসময় চলে। সমস্যা হলো, aggregation-এর ভুল প্রায় কখনো ত্রুটি দেয় না — ফল আসে, শুধু সংখ্যাটি ভুল। তাই AI-এর লেখা aggregate কোয়েরিতে যে ভুলগুলো সবচেয়ে বেশি দেখা যায়:

AI-এর লেখা aggregate কোয়েরি হাতে পেলে — যাচাইয়ের চেকলিস্ট

• SELECT, HAVING আর ORDER BY-এর প্রতিটি কলাম GROUP BY-তে বা aggregate-এর ভেতরে আছে
• JOIN থাকলে JOIN-এর আগে আর পরে সারির সংখ্যা তুলনা করেছি; গোনার জন্য দরকারমতো COUNT(DISTINCT …)
• দলগুলোর COUNT/SUM-এর যোগফল GROUP BY ছাড়া মোট COUNT/SUM-এর সাথে মিলিয়েছি
• NULL-ওয়ালা সারি আর একেবারে ফাঁকা সেট (কোনো অর্ডার নেই এমন গ্রাহক) দিয়ে চালিয়ে দেখেছি
• সীমানার মান (ঠিক 5000) আর “আয়”-এর মতো শব্দের সংজ্ঞা প্রশ্নকর্তার সাথে মিলিয়েছি
• গড়ের গড় নেই; ভারিত গড় দরকার হলে SUM ÷ SUM
• বড় টেবিলে EXPLAIN দেখেছি — সারির শর্ত WHERE-এ, index কাজে লাগছে (পর্ব ৪১)