CODEWITHSUNDEEP
mysqlsql-server
Anshul Kalra
Anshul Kalra

Reputation: 198

more than one count* sql in a single sql

i want to use these sql statement as a single statement which will result in 4columns with different count and a company_id. 

select company_id,count(*) as det from feedbacks a join( select id,company_id from users  ) b on a.user_id = b.id where healthi_rating < 7 group by company_id
select company_id,count(*) as neu from feedbacks a join( select id,company_id from users  ) b on a.user_id = b.id where healthi_rating <9 && healthi_rating >=7 group by company_id
select company_id,count(*) as pro from feedbacks a join( select id,company_id from users  ) b on a.user_id = b.id where healthi_rating >=9 group by company_id

can someone let me know how to do this.

Upvotes: 0

Views: 62

Answers (1)

juergen d
juergen d

Reputation: 204746

For MySQL do

select company_id,
       sum(healthi_rating < 7) as det,
       sum(healthi_rating between 7 and 8) as neu,
       sum(healthi_rating >= 9) as pro
from feedbacks a 
join( select id, company_id from users ) b on a.user_id = b.id 
group by company_id

and using ANSI SQL Standard and without the subquery of users

select b.company_id,
       sum(case when healthi_rating < 7 then 1 else 0 end) as det,
       sum(case when healthi_rating between 7 and 8 then 1 else 0 end) as neu,
       sum(case when healthi_rating >=9 then 1 else 0 end) as pro
from feedbacks a 
join users b on a.user_id = b.id 
group by b.company_id

Upvotes: 4

Related Questions