🔍 1. Background - क्यों ज़रूरत पड़ी GROUPING SETS की?
जब हमें एक ही query में अलग-अलग तरीके से data को group करके summary चाहिए होती है, तब हम GROUPING SETS, ROLLUP या CUBE का use करते हैं।
🚫 पहले कैसे करना पड़ता था?
मान लीजिए, हमें employee count चाहिए:
- सिर्फ
BranchIDपर - सिर्फ
Genderपर BranchIDऔरGenderदोनों पर- और total summary भी चाहिए
तो हमें 4 अलग-अलग queries चलानी पड़ती थीं।
✅ अब एक ही query से:
GROUP BY GROUPING SETS ((BranchID), (Gender), (BranchID, Gender), ())
अब एक ही बार में चारों summary मिल जाती है।
🧠 2. GROUPING SETS - Custom Grouping Combinations
SELECT BranchID, Gender, COUNT(*) AS TotalEmployee
FROM EmployeeDetails
GROUP BY GROUPING SETS (
(BranchID),
(Gender),
(BranchID, Gender),
()
);
NULL का मतलब होता है कि उस column में aggregate result दिया जा रहा है।
🔁 3. ROLLUP - Hierarchy-Based Summary
ROLLUP tab use करते हैं जब hierarchy हो — जैसे: Country → State → City
या BranchID → Gender (जैसे पहले branch का total, फिर gender-wise)
SELECT BranchID, Gender, COUNT(*) AS TotalEmployee
FROM EmployeeDetails
GROUP BY ROLLUP (BranchID, Gender)
यह grouping sets को internally generate करता है:
- (BranchID, Gender)
- (BranchID)
- ()
मतलब hierarchy में ऊपर से नीचे की तरफ summarize करता है।
🔲 4. CUBE - All Possible Combinations
CUBE सब combinations बना देता है:
SELECT BranchID, Gender, COUNT(*) AS TotalEmployee
FROM EmployeeDetails
GROUP BY CUBE (BranchID, Gender)
इससे ये 4 grouping sets मिलते हैं:
- (BranchID, Gender)
- (BranchID)
- (Gender)
- ()
CUBE = full cross summary across all dimensions
🧮 5. GROUPING() और GROUPING_ID() क्या करते हैं?
जब आप aggregated rows देखते हैं (जैसे NULL in BranchID), तो पहचानने के लिए:
🧾 GROUPING():
SELECT
BranchID,
Gender,
GROUPING(BranchID) AS BranchGrouping,
GROUPING(Gender) AS GenderGrouping,
COUNT(*) AS TotalEmployee
FROM EmployeeDetails
GROUP BY ROLLUP(BranchID, Gender);
🔢 GROUPING(ColumnName) का मतलब:
| Value | मतलब |
|---|---|
0 | यह column group by clause का हिस्सा है → यानी actual value use हो रही है |
1 | यह column group by clause का हिस्सा नहीं है → यानी summary row है (value NULL होगी) |
🧾 GROUPING_ID():
SELECT
GROUPING_ID(BranchID, Gender) AS GroupID,
BranchID,
Gender,
COUNT(*) AS TotalEmployee
FROM EmployeeDetails
GROUP BY CUBE(BranchID, Gender);
GROUPING_ID()से unique ID मिलती है हर combination के लिए:- (BranchID, Gender) → 0
- (BranchID) → 1
- (Gender) → 2
- () → 3
💼 Real-World Use Case: Report with All Summaries
Imagine an HR Dashboard where you want:
- Total employees per branch
- Total employees per gender
- Branch-wise male/female breakdown
- Grand total
Without GROUPING SETS:
Multiple UNION queries 😩
With GROUPING SETS / CUBE / ROLLUP:
One clean query 😎
✍️ Bonus: Query Enhancement with Labeling
SELECT
BranchID,
Gender,
COUNT(*) AS TotalEmployee,
CASE
WHEN GROUPING_ID(BranchID, Gender) = 0 THEN 'Branch + Gender'
WHEN GROUPING_ID(BranchID, Gender) = 1 THEN 'Branch Only'
WHEN GROUPING_ID(BranchID, Gender) = 2 THEN 'Gender Only'
WHEN GROUPING_ID(BranchID, Gender) = 3 THEN 'Grand Total'
END AS GroupLabel
FROM EmployeeDetails
GROUP BY CUBE(BranchID, Gender)
ORDER BY GroupLabel;
✅ Summary
| Clause | Use For | Generates |
|---|---|---|
GROUPING SETS | Custom groupings you define | Exact you write |
ROLLUP | Hierarchical rollups (top-down) | Subsets + Grand total |
CUBE | All combinations (cross-dimensional) | All possibilities |
GROUPING() | Flags which column is grouped (1/0) | Debugging & Labeling |
GROUPING_ID() | Unique ID for grouped columns | Sorting/Labeling |
ORDER BY GROUPING(Column)
SELECT
BranchID,
Gender,
COUNT(*) AS TotalEmployees,
GROUPING(BranchID) AS IsBranchGrouped,
GROUPING(Gender) AS IsGenderGrouped
FROM EmployeeDetails
GROUP BY CUBE(BranchID, Gender)
ORDER BY GROUPING(BranchID), GROUPING(Gender);
