I have questions about SQL Server query for SUM.
I have 2 tables:
1) EmployeesAtt (EId, EName, Stats) -> I have some criteria for Stats (0 = late, 1 = overtime, 2 = normal)
----------------------------- | EId | EName | Stats | ----------------------------- | 1 | John | 0 | |---------------------------| | 1 | John | 1 | |---------------------------| | 1 | John | 0 | |---------------------------| | 2 | Lynda | 2 | -----------------------------
2) AttLogs (ID, EId, late, overtime, normal)
From EmployeesAtt table, I want to SUM the EId And Stats in dedicated column in AttLogs looks like this one:
-------------------------------------------------- | ID | EId | late | overtime | Normal | -------------------------------------------------- | 0 | John | 2 | 1 | 0 | |------------------------------------------------| | 1 | Lynda | 0 | 0 | 1 | --------------------------------------------------
this is what I’ve done so far:
select EA.EId, EA.EName, late = case when Stats = 0 then SUM(Stats) END, overtime = case when Stats = 1 then SUM(Stats) END, normal = case when Stats = 2 then SUM(Stats) END From EmployeesAtt EA JOIN AttLogs AL ON AL.EId = EA.EId GROUP BY EA.EId, EA.EName, EA.Stats
but the results are not as I expected above.
Advertisement
Answer
You need to use conditional aggregation and sum/count your CASE
expressions:
SELECT ea.EId, ea.EName, COUNT(CASE WHEN Stats = 0 THEN 1 END) AS late, COUNT(CASE WHEN Stats = 1 THEN 1 END) AS overtime, COUNT(CASE WHEN Stats = 2 THEN 1 END) AS normal FROM EmployeesAtt ea INNER JOIN AttLogs al ON al.EId = ea.EId GROUP BY ea.EId, ea.EName;