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 कैसे करें?”