CODEWITHSUNDEEP
sql-server
Roelant M
Roelant M

Reputation: 1706

sql statement combine counting 2 statements results

I'm having trouble with combining the 2 statements.

I have a festivalTable which you can vote on => voteTable. and for special occasions you can also have a specialVoteTable

for the 2 seperate tables i have: 

select festivalId as festId, COUNT(festivalId) as total from Vote 
where active = 'J'  and FestivalId = 593
group by FestivalId

the same for the other table. Now I need to combine these 2 results, with the exception that from the specialvote i need to multiply it by 8, so i can devide the total by 9.

so the 2 seperate queries are

select festivalId as festId, COUNT(festivalId) as total from Vote 
where active = 'J'  and FestivalId = 593
group by FestivalId

select festivalId as festId, (COUNT(festivalId) * 8) as total from       SpecialChartVote 
where active = 'J'  and FestivalId = 593
group by FestivalId

(not combined with the festivalTable yet.)

Makes any sense?

Upvotes: 0

Views: 18

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your query, as written, is only looking for one festival. That seems strange, but you can combine the queries by doing:

select 593 as festId,
       ( (select count(*) from Vote where active = 'J'  and FestivalId = 593) +
         (select count(*) * 8 from SpecialChartVote where active = 'J' and FestivalId = 593)
       ) / 9.0

SQLZims answer is definitely better if you want to get the information for multiple festivals. I just want to point you that you can "combine" your queries essentially by doing:

select (<query1> + <query2>)

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

using union all in a subquery:

select festId, sum(total)/9 as total
from 
  (
  select 
      festivalId as festId
    , COUNT(festivalId) as total 
  from Vote 
  where active = 'J'  and FestivalId = 593
  group by FestivalId
  union all 
  select 
      festivalId as festId
    , (COUNT(festivalId) * 8) as total 
  from SpecialChartVote 
  where active = 'J'  and FestivalId = 593
  group by FestivalId
  ) u
group by festId

Upvotes: 2

Related Questions