CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Michael Felchlin
Michael Felchlin

Reputation: 91

Sum all rows that are sum of a column sql 2012

I want to get a total sum of the results of my "total tested" "failed" and "passed" colums. I would usually handle these aggregates in SSRS, but I don't have that option at this time.

My current query:

select s.school_code, s.name, count(cd.Test_ID) as [Tested],
 sum(case when cd.result = 'fail' then 1 Else 0 End) as 'Failed'
 ,sum(case when cd.result = 'pass' then 1 Else 0 End) as 'Passed'

FROM 
 [psi_db_8amSnapshot].[dbo].[Candidate_Data] cd
  join [psi_db_8amSnapshot].[dbo].account_school s on s.school_id = cd.school_id
  where s.School_code in 
  (
  '1001', 
'1002',
'1003' ,
'1004' ,
'1005' ,
'1006' ,
'1007' ,
'1008' ,
'1016' ,
'1009' ,
'1010' ,
'1012' ,
'1013' ,
'1014' ,
'1015'
)

and cd.[date] between '01-01-2016' and '05-01-2017' and cd.TestName = 'MN Dental Assistant State Licensure Examination'
group by s.school_code, s.name, test_id

I am looking to get a total off all the values in my three aggregate columns. So a sum of Tested, which should = 640, sum of passed = 327, sum of failed = 313.

Upvotes: 0

Views: 111

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

I think you are looking for group by grouping sets as below:

Just replace your group by as below:

group by grouping sets ((s.school_code), (s.name), (test_id))

Upvotes: 1

Ben
Ben

Reputation: 2040

I would probably use with statements:

with NumberOfFailedResults as
(
    Select count(cd.result) as NumberOfFailedResults from [psi_db_8amSnapshot].[dbo].[Candidate_Data] as cd where cd.Result = 'Failed'
),
NumberOfPassedResults as
(
    Select count(cd.result) as NumberOfPassedResults from [psi_db_8amSnapshot].[dbo].[Candidate_Data] as cd where cd.Result = 'Passed'
)

Select NumberOfFailedResults, NumberOfPassedResults, ...

Upvotes: 1

Varun Mehta
Varun Mehta

Reputation: 144

This should work for you.

select Q.school_code, Q.name, SUM(Q.Tested), SUM(Q.Failed), SUM(Q.Passed) from  (   
select s.school_code, s.name, count(cd.Test_ID) as [Tested],
 sum(case when cd.result = 'fail' then 1 Else 0 End) as 'Failed'
 ,sum(case when cd.result = 'pass' then 1 Else 0 End) as 'Passed'

FROM 
 [psi_db_8amSnapshot].[dbo].[Candidate_Data] cd
  join [psi_db_8amSnapshot].[dbo].account_school s on s.school_id = cd.school_id
  where s.School_code in 
  (
  '1001', 
'1002',
'1003' ,
'1004' ,
'1005' ,
'1006' ,
'1007' ,
'1008' ,
'1016' ,
'1009' ,
'1010' ,
'1012' ,
'1013' ,
'1014' ,
'1015'
)

and cd.[date] between '01-01-2016' and '05-01-2017' and cd.TestName = 'MN Dental Assistant State Licensure Examination'
group by s.school_code, s.name, test_id)Q
GROUP BY Q.School_Code, Q.Name

Upvotes: 0

Related Questions