CODEWITHSUNDEEP
sqlsql-server
Yohan Wijayanto
Yohan Wijayanto

Reputation: 23

Sum By Criteria in specific column

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.

Upvotes: 0

Views: 53

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

Maximize the use of iif function in sql server. 

select EA.EId
    , EA.EName
    , late = sum(iif(Stats=0, 1, 0))
    , overtime = sum(iif(Stats=0, 1, 0))
    , normal = sum(iif(Stats=0, 1, 0))
From EmployeesAtt EA 
JOIN AttLogs AL ON AL.EId = EA.EId
GROUP BY EA.EId, EA.EName

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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;

Upvotes: 1

Related Questions