CODEWITHSUNDEEP
sqlselect
Stan
Stan

Reputation: 595

Combining Select Statements

I have three select statements that provide the number of Members that attend particular events - Healthcare, Religious and Sport - in addition to the average "score" for each event and a count for each.

The count will differ for each event.

Individually each query works, but I want to combine them into one query.

How do I do that? 

 (select sum(case when Healthcare ='1' then 1 else 0 end)  as [Healthcare_never],
 sum(case when Healthcare ='2' then 1 else 0 end)  as [Healthcare_not often],
 sum(case when Healthcare ='3' then 1 else 0 end)  as [Healthcare_average],
 sum(case when Healthcare ='4' then 1 else 0 end)  as [Healthcare_often],
 sum(case when Healthcare ='5' then 1 else 0 end)  as [Healthcare_very often]
 ,avg(Cast(Healthcare as float)) as Average
 ,count(Healthcare) as N_Healthcare
 from Member 
 where Healthcare > '0' )


 (select
  sum(case when Religious ='1' then 1 else 0 end)  as [Religious_never],
  sum(case when Religious ='2' then 1 else 0 end)  as [Religious_not often],
  sum(case when Religious ='3' then 1 else 0 end)  as [Religious_average],
  sum(case when Religious ='4' then 1 else 0 end)  as [Religious_often],
  sum(case when Religious ='5' then 1 else 0 end)  as [Religious_very often],
  Avg(cast(Religious as float)) as Average
  ,count(Religious) as N_Religious
  from Member 
  where Religious > '0' )


 (select
 sum(case when Sport ='1' then 1 else 0 end)  as [Sport_never],
 sum(case when Sport ='2' then 1 else 0 end)  as [Sport_not often],
 sum(case when Sport ='3' then 1 else 0 end)  as [Sport_average],
 sum(case when Sport ='4' then 1 else 0 end)  as [Sport_often],
 sum(case when Sport ='5' then 1 else 0 end)  as [Sport_very often],
 Avg(cast(Sport as float)) as Average
 ,count(Sport) as N_Sport
 from Member 
 where Sport > '0' )

Upvotes: 0

Views: 86

Answers (3)

Stan
Stan

Reputation: 595

The problem is the where statement provides an incorrect count for each event. The Healthcare count should be 54, Sport 54 and Other 13 Instead the count of all is 55. 

 select 
 sum(case when Healthcare ='1' then 1 else 0 end)  as [Healthcare_never],
 sum(case when Healthcare ='2' then 1 else 0 end)  as [Healthcare_not often],
 sum(case when Healthcare ='3' then 1 else 0 end)  as [Healthcare_average],
 sum(case when Healthcare ='4' then 1 else 0 end)  as [Healthcare_often],
 sum(case when Healthcare ='5' then 1 else 0 end)  as [Healthcare_very often],
 avg(Cast(Healthcare as float)) as Average,
 count(Healthcare) as N_Healthcare,

 sum(case when Religious ='1' then 1 else 0 end)  as [Religious_never],
 sum(case when Religious ='2' then 1 else 0 end)  as [Religious_not often],
 sum(case when Religious ='3' then 1 else 0 end)  as [Religious_average],
 sum(case when Religious ='4' then 1 else 0 end)  as [Religious_often],
 sum(case when Religious ='5' then 1 else 0 end)  as [Religious_very often],
 Avg(cast(Religious as float)) as Average,
 count(Religious) as N_Religious,

 sum(case when Sport ='1' then 1 else 0 end)  as [Sport_never],
 sum(case when Sport ='2' then 1 else 0 end)  as [Sport_not often],
 sum(case when Sport ='3' then 1 else 0 end)  as [Sport_average],
 sum(case when Sport ='4' then 1 else 0 end)  as [Sport_often],
 sum(case when Sport ='5' then 1 else 0 end)  as [Sport_very often],
 Avg(cast(Sport as float)) as Average,
 count(Sport) as N_Sport
 from contacts_cstm 
 where Sport > '0' or Religious > '0' or Healthcare >'0'

Upvotes: 1

cindy
cindy

Reputation: 21

Just put UNION ALL between the SELECT statements. But you'll probably want to add another column to indicate which table it came from. And the column headers need to be the same. For example:

 select 'Healthcare' as source
, sum(case when Healthcare ='1' then 1 else 0 end)  as [never], 
 sum(case when Healthcare ='2' then 1 else 0 end)  as [not often], 
 sum(case when Healthcare ='3' then 1 else 0 end)  as [average], 
 sum(case when Healthcare ='4' then 1 else 0 end)  as [often], 
 sum(case when Healthcare ='5' then 1 else 0 end)  as [very often] 
 ,avg(Cast(Healthcare as float)) as Average 
 ,count(Healthcare) as N_Healthcare 
 from Member  
 where Healthcare > '0' 
UNION ALL

etc.

Upvotes: 2

rs.
rs.

Reputation: 27467

try this

select 
sum(case when Healthcare ='1' then 1 else 0 end)  as [Healthcare_never],
sum(case when Healthcare ='2' then 1 else 0 end)  as [Healthcare_not often],
sum(case when Healthcare ='3' then 1 else 0 end)  as [Healthcare_average],
sum(case when Healthcare ='4' then 1 else 0 end)  as [Healthcare_often],
sum(case when Healthcare ='5' then 1 else 0 end)  as [Healthcare_very often],
avg(Cast((case when Healthcare > 0 Then Healthcare Else Null end) as float)) as Healthcare_Average,
count(case when Healthcare > 0 Then Healthcare Else Null end) as N_Healthcare,

sum(case when Religious ='1' then 1 else 0 end)  as [Religious_never],
sum(case when Religious ='2' then 1 else 0 end)  as [Religious_not often],
sum(case when Religious ='3' then 1 else 0 end)  as [Religious_average],
sum(case when Religious ='4' then 1 else 0 end)  as [Religious_often],
sum(case when Religious ='5' then 1 else 0 end)  as [Religious_very often],
Avg(cast((case when Religious > 0 Then Religious Else Null end) as float)) as Religious_Average,
count(case when Religious > 0 Then Religious Else Null end) as N_Religious,

sum(case when Sport ='1' then 1 else 0 end)  as [Sport_never],
sum(case when Sport ='2' then 1 else 0 end)  as [Sport_not often],
sum(case when Sport ='3' then 1 else 0 end)  as [Sport_average],
sum(case when Sport ='4' then 1 else 0 end)  as [Sport_often],
sum(case when Sport ='5' then 1 else 0 end)  as [Sport_very often],
Avg(cast((case when Sport > 0 Then Sport Else Null end) as float)) as Sport_Average,
count(case when Sport > 0 Then Sport Else Null end) as N_Sport

from Member

Upvotes: 3

Related Questions