SQL एग्रीगेशन और GROUP BY

यह क्यों ज़रूरी है

कई data defects एक-एक row में नहीं दिखते: ऐसा order जिसका total उसकी lines से अलग हो, ऐसा import जिसने 10,000 में से 9,998 rows load कीं, ऐसी report जो cancelled orders गिनती हो। हर row ठीक लगती है; defect तभी दिखता है जब set को गिनकर या जोड़कर expected value से मिलाया जाए।

एक aggregate query ऐसी report, invoice run या migration जाँच लेती है जिसे कोई row-by-row नहीं पढ़ सकता। खतरा गलत query का है: छूटा हुआ NULL, rows दोहराने वाला join या गलत clause में रखा filter false pass या false alarm देता है।

Aggregate functions: COUNT, SUM, AVG, MIN और MAX

Aggregate function rows के एक set के लिए एक value लौटाता है। GROUP BY के बिना WHERE पार करने वाली सारी rows एक set होती हैं, इसलिए query ठीक एक row लौटाती है, चाहे कुछ match न हुआ हो। Code block इस पूरे lesson में इस्तेमाल होने वाली छह rows की orders table दिखाता है।

Testing के लिए अहम बात NULL है: COUNT(*) को छोड़कर हर aggregate उसे ignore करता है। तीन orders की rating नहीं है, इसलिए COUNT(rating) 3 है और AVG(rating) 12 / 3 = 4 है, 12 / 6 = 2 नहीं। अगर business rule गायब value को शून्य मानता है, तो AVG(COALESCE(rating, 0)) लिखें और test case में यह बात दर्ज करें।

• COUNT(*) rows गिनता है, COUNT(column) non-NULL values, और COUNT(DISTINCT column) अलग-अलग non-NULL values।
• शून्य rows पर, या सिर्फ़ NULLs पर, SUM 0 नहीं बल्कि NULL लौटाता है: COALESCE(SUM(amount), 0) से तुलना करें।
• SQL Server में integer column पर AVG integer लौटाता है; पहले decimal type में cast करें।
• MIN और MAX numbers, dates और text पर चलते हैं, इसलिए range की तेज़ जाँच हैं, जैसे किसी import का सबसे पुराना created_at।

-- orders: the example table used in this lesson
-- order_id  customer_id  status     total_amount  rating
-- 1001      7            PAID             120.00       5
-- 1002      7            PAID              80.00    NULL
-- 1003      9            CANCELLED         50.00    NULL
-- 1004      9            PAID             200.00       4
-- 1005      12           PENDING           35.00    NULL
-- 1006      12           PAID              15.00       3

SELECT COUNT(*)                    AS all_rows,       -- 6
       COUNT(rating)               AS rated,          -- 3
       COUNT(DISTINCT customer_id) AS customers,      -- 3
       SUM(total_amount)           AS amount,         -- 500.00
       AVG(rating)                 AS avg_rating,     -- 4 = 12 / 3, not 12 / 6
       MIN(total_amount)           AS smallest,       -- 15.00
       MAX(total_amount)           AS largest         -- 200.00
FROM orders;

आज़माएँ: SQL INSERT जनरेटर आज़माएँ: SQL डेटासेट जनरेटर

GROUP BY: हर group के लिए result की एक row

GROUP BY grouping columns में एक जैसी value वाली rows को एक group में रखता है और हर aggregate हर group के लिए एक बार निकालता है। GROUP BY status छह orders को PAID (4 rows), CANCELLED (1) और PENDING (1) में बाँटता है; customer_id, status पर grouping हर combination का एक group बनाती है।

SELECT list का हर column या तो grouped हो या aggregated। SELECT status, order_id, COUNT(*) … GROUP BY status एक order_id माँगती है जबकि PAID में चार हैं। PostgreSQL, SQL Server और अपने default ONLY_FULL_GROUP_BY mode में MySQL इसे reject करते हैं; SQLite किसी arbitrary row की value लौटा देता है, इसलिए check संयोग से सही दिख सकता है।

• Grouping column में NULL अपना अलग group बनाता है, जो अक्सर अपने आप में एक finding है।
• Groups का कोई तय क्रम नहीं होता: report से तुलना से पहले ORDER BY जोड़ें।
• Groups के totals का जोड़ पूरे total के बराबर होना चाहिए: 50.00 + 415.00 + 35.00 = 500.00 दिखाता है कि कोई row न खोई, न दो बार गिनी गई।

SELECT status,
       COUNT(*)          AS orders,
       SUM(total_amount) AS amount
FROM orders
GROUP BY status
ORDER BY status;

-- status     orders  amount
-- CANCELLED       1   50.00
-- PAID            4  415.00
-- PENDING         1   35.00

आज़माएँ: SQL क्वेरी वैलिडेटर आज़माएँ: SQL फ़ॉर्मेटर

WHERE rows को filter करता है, HAVING groups को

Grouped query एक logical क्रम में evaluate होती है: FROM और JOIN, WHERE, GROUP BY, aggregates, HAVING, SELECT, ORDER BY। Engine इसे अलग तरीके से चला सकता है, पर result हमेशा ऐसा होता है मानो यही क्रम माना गया हो। इसीलिए WHERE aggregate इस्तेमाल नहीं कर सकता, क्योंकि groups अभी बने ही नहीं, और HAVING कर सकता है।

अकेली rows की conditions, जैसे status या date range, WHERE में रखें, और group results की conditions, जैसे COUNT(*) > 1, HAVING में। नीचे की query एक से ज़्यादा paid orders वाले customers ढूँढती है: सिर्फ़ customer 7, दो orders और 200.00 के साथ। WHERE line हटाएँ तो customers 9 और 12 भी आ जाते हैं, क्योंकि अब उनके cancelled और pending orders भी गिने जाते हैं।

यही ढाँचा duplicates की आम जाँच है: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1 हर वह email address दिखाती है जो एक से ज़्यादा customers पर दर्ज है।

-- Customers with more than one PAID order
SELECT customer_id,
       COUNT(*)          AS paid_orders,
       SUM(total_amount) AS paid_amount
FROM orders
WHERE status = 'PAID'      -- row filter, before grouping
GROUP BY customer_id
HAVING COUNT(*) > 1;       -- group filter, after aggregation

-- customer_id  paid_orders  paid_amount
-- 7                      2       200.00

Reconciliation checks: दो संख्याएँ जिन्हें मेल खाना चाहिए

Reconciliation एक ही count या total दो जगह निकालकर उनकी तुलना करता है। दूसरी जगह test oracle है, यानी expected result का source: document का header बनाम उसकी lines, import के बाद source system बनाम target, database बनाम कोई report या API response।

Check ऐसे लिखें कि सही database शून्य rows लौटाए: साझा key पर group करें और HAVING से सिर्फ़ असहमत groups रखें। लौटी हर row bug report का सबूत है। सिर्फ़ grand total नहीं, हर group की तुलना करें, क्योंकि grand total में दो उलटी गलतियाँ एक-दूसरे को काट सकती हैं।

• Join fan-out: orders को उनकी lines से join करने पर हर order उतनी बार दोहराया जाता है जितनी उसकी lines हैं, इसलिए join के बाद SUM(o.total_amount) total बढ़ा देता है। Code block की तरह हर order की lines का aggregate निकालें।
• गायब rows: बिना lines वाला order inner join से गायब हो जाता है। LEFT JOIN उसे रखता है, पर उसका SUM NULL होता है और total_amount <> NULL कभी true नहीं होता, इसलिए HAVING उसे फिर भी हटा देता है; COALESCE NULL को 0 बना देता है और mismatch दिख जाता है।

-- Rule: an order's total_amount equals the sum of its item lines
SELECT o.order_id,
       o.total_amount,
       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS lines_total
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> COALESCE(SUM(oi.quantity * oi.unit_price), 0);

-- Zero rows: the rule holds. Every row returned is a mismatch to report.

Worked example: daily sales report का reconciliation

Requirement: "Daily sales report हर दिन के PAID orders की संख्या और उनकी कुल राशि Riyadh time में दिखाती है।" created_at Riyadh time में stored है, इसलिए आप 1 से 3 सितंबर के paid orders को date के हिसाब से group करके हर दिन को report से मिलाते हैं। CAST(created_at AS DATE) समय वाला हिस्सा हटा देता है, इसलिए एक तारीख के सारे orders एक group में आते हैं।

दो दिन match करते हैं; 2 सितंबर नहीं। Count और amount दोनों का अंतर गलत amounts की बजाय extra rows का संकेत है, इसलिए उस दिन को हर status के हिसाब से group करके खोलें।

• Database: 38 orders, 5720.50। Report: 40 orders, 5905.50। अंतर: 2 orders, 185.00।
• Status के हिसाब से: CANCELLED में 2 orders और 185.00, ठीक-ठीक अंतर। आँकड़ों का मेल पक्का सबूत नहीं, मज़बूत संकेत है: अगर report अपने orders की list दिखा सकती है, तो जाँचें कि ये दोनों orders उसमें हैं।
• Bug report: लगता है daily sales में payment के बाद cancel हुए orders भी गिने जा रहे हैं। दोनों queries और उनके results attach करें, और product owner से पूछें कि ऐसे orders daily sales में आने चाहिए या नहीं, क्योंकि requirement यह नहीं बताती।

-- 1. Expected values: PAID orders per day, 1 to 3 September
--    created_at holds Riyadh local time; in SQLite write date(created_at)
SELECT CAST(created_at AS DATE) AS sales_day,
       COUNT(*)                 AS paid_orders,
       SUM(total_amount)        AS paid_amount
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-09-01' AND created_at < '2026-09-04'
GROUP BY CAST(created_at AS DATE)
ORDER BY sales_day;

-- sales_day   database        report          result
-- 2026-09-01  41  6150.00     41  6150.00     match
-- 2026-09-02  38  5720.50     40  5905.50     mismatch
-- 2026-09-03  44  6480.00     44  6480.00     match

-- 2. Drill down into 2 September, every status
SELECT status, COUNT(*) AS orders, SUM(total_amount) AS amount
FROM orders
WHERE created_at >= '2026-09-02' AND created_at < '2026-09-03'
GROUP BY status
ORDER BY status;

-- CANCELLED   2   185.00    <- exactly the difference
-- PAID       38  5720.50
-- PENDING     3   240.00

आज़माएँ: SQL VALUES जनरेटर आज़माएँ: CSV से SQL

अक्सर पूछे जाने वाले सवाल

संदर्भ

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupPostgreSQL में SQL की लेक्सिकल संरचना: आइडेंटिफ़ायर, क्वोटिंग, स्ट्रिंग कॉन्स्टेंट और एस्केपिंग।
• SQL As Understood By SQLiteSQLiteSQLite द्वारा स्वीकृत SQL व्याकरण, स्टेटमेंट दर स्टेटमेंट, सिंटैक्स डायग्राम के साथ।
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023अंतरराष्ट्रीय SQL मानक का framework भाग, जिसे PostgreSQL, MySQL, SQLite आदि की बोलियाँ अलग-अलग हद तक लागू करती हैं।
• ISTQB GlossaryISTQBटेस्टिंग शब्दों की मानक परिभाषाएँ — टेस्ट केस, रिग्रेशन टेस्टिंग, सेवेरिटी, प्रायोरिटी, बाउंड्री वैल्यू एनालिसिस आदि।