如何在单个查询中编写具有多个where条件的多个select语句？

Question

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 1

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