CODEWITHSUNDEEP
sql-server
Archana N
Archana N

Reputation: 35

Query for sum of all and particular rows

How can I arrive at a query for the below scenario?

Data:

Date        Product  Result  Total
15/01/2015  ABC      Pass     5 
15/01/2015  XYZ      Pass     8 
15/01/2015  MNO      Fail     2 
23/01/2015  ABC      Pass    10 
23/01/2015  XYZ      Fail     3

I need the result in the below format:

Date        Total  Pass  Fail 
15/01/2015  15     13    2 
23/01/2015  13     10    3

Upvotes: 1

Views: 41

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93744

Use conditional Aggregate

select Date  
       sum(Total) Total,
       SUM(case when Result  ='Pass' then Total else 0 end) Pass,
       SUM(case when Result  ='Fail' then Total else 0 end) Fail
From yourtable
Group by Date

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Try this using PIVOT . FIDDLER DEMO

SELECT Date, 
      sum(pass) + sum(fail) AS Total, 
      sum(pass) AS Pass, 
      sum(fail) AS Fail 
FROM TableName
PIVOT (SUM(Total) FOR Result in (pass, fail)) AS P
GROUP BY Date

Upvotes: 1

Related Questions