SQL Server में COUNT और SUM + CASE से गिनती कैसे करें?
अगर आप SQL Server में Reports बना रहे हैं और आपको Male/Female, Married/Unmarried या अन्य किसी Category की गिनती चाहिए, तो SUM + CASE आपके लिए सबसे Best तरीका है।
✅ Example #1: Male / Female Count
मान लीजिए आपकी Users टेबल है:
| UserID | Name | Gender |
|---|---|---|
| 1 | राम | Male |
| 2 | सीता | Female |
| 3 | अर्जुन | Male |
SELECT
COUNT(*) AS TotalUsers,
SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS MaleCount,
SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS FemaleCount
FROM Users;
✅ Example #2: Senior Citizens Count (Age ≥ 60)
SELECT
COUNT(*) AS TotalUsers,
SUM(CASE WHEN Age >= 60 THEN 1 ELSE 0 END) AS SeniorCitizens
FROM Users;
✅ Example #3: Married / Unmarried Count
SELECT
COUNT(*) AS Total,
SUM(CASE WHEN MaritalStatus = 'Married' THEN 1 ELSE 0 END) AS Married,
SUM(CASE WHEN MaritalStatus = 'Unmarried' THEN 1 ELSE 0 END) AS Unmarried
FROM Users;
✅ Example #4: Applications Count (Accepted / Rejected / Pending)
SELECT
COUNT(*) AS TotalApplications,
SUM(CASE WHEN Status = 'Accepted' THEN 1 ELSE 0 END) AS AcceptedCount,
SUM(CASE WHEN Status = 'Rejected' THEN 1 ELSE 0 END) AS RejectedCount,
SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS PendingCount
FROM Applications;
💡 Tip: जब भी आपको किसी condition पर count करना हो, तो
SUM(CASE WHEN ...) SQL Server में best practice मानी जाती है।
📋 Summary Table:
| Example | किसकी गिनती हो रही है? | Method |
|---|---|---|
| #1 | Male / Female | SUM(CASE WHEN Gender = '...') |
| #2 | Senior Citizens | SUM(CASE WHEN Age ≥ 60) |
| #3 | Married / Unmarried | SUM(CASE WHEN MaritalStatus = '...') |
| #4 | Status Wise | SUM(CASE WHEN Status = '...') |
✅ BONUS: NULL Values को Handle कैसे करें?
SUM(CASE WHEN ISNULL(Status, '') = 'Pending' THEN 1 ELSE 0 END)
👉 अगले ब्लॉग में: हम सीखेंगे “Group By के साथ COUNT कैसे करें?”
0 Comments