mahesh - 9 months ago 29

SQL 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

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
```