mahesh mahesh - 3 months ago 10
SQL Question

How to write multiple select statements with multiple where condition in single query?

I am working on php with sql .I have one teachers_rating table in database I want to get ratings like how many members are give 5 rating like that only 4, like 3, like so...I used this sql query check once.

SELECT COUNT( teachers_rating.rating ) AS fivestar
FROM teachers_rating
WHERE rating = '5' and id='23'
UNION
SELECT COUNT( teachers_rating.rating ) AS fourstar
FROM teachers_rating
WHERE rating = '4'and id='23'
UNION
SELECT COUNT( teachers_rating.rating ) AS threestar
FROM teachers_rating
WHERE rating = '3'and id='23'

like that its running but out output is
fivestar
18
14
20

but iwant this type of structure
fivestar fourstar threestar
18 14 20


If any one knows please help me.

Answer

This is called conditional aggregation :

SELECT COUNT(CASE WHEN t.rating =5 THEN 1 END) as five,
       COUNT(CASE WHEN t.rating =4 THEN 1 END) as four,
       COUNT(CASE WHEN t.rating =3 THEN 1 END) as three
FROM teachers_rating t
WHERE t.id = '23'

If you want it dynamic for all IDs :

SELECT t.id,
       COUNT(CASE WHEN t.rating =5 THEN 1 END) as five,
       COUNT(CASE WHEN t.rating =4 THEN 1 END) as four,
       COUNT(CASE WHEN t.rating =3 THEN 1 END) as three
FROM teachers_rating t
GROUP BY t.id
Comments