Ashish Chaturvedi Ashish Chaturvedi - 4 months ago 9
SQL Question

How to shift column name to result set in MySQL?

I have a query

SELECT
count(CASE WHEN fan_gate.genre = 'Breaks' THEN fan_gate.id END) AS 'Breaks',
count(CASE WHEN fan_gate.genre = 'Cill Out' THEN fan_gate.id END) AS 'Chill Out',
count(CASE WHEN fan_gate.genre = 'Deep House' THEN fan_gate.id END) AS 'Deep House',
count(CASE WHEN fan_gate.genre = 'Drum & Bass' THEN fan_gate.id END) AS 'Drum & Bass'
FROM fan_gate


Current result set

Breaks | Cill Out |Deep House |Drum & Bass
------------------------------------------------
4581 | 785 | 7858 | 4474


I need

Column 1 | column 2
-----------------------
Breaks | 4581
Cill Out | 785
Deep House | 7858
Drum & Bass | 4474

Answer

You just want group by:

SELECT fg.genre, COUNT(*)
FROM fan_gate fg
GROUP BY fg.genre;

If you only care about those four genres, then add a WHERE clause before the GROUP BY to select the ones you care about.

Comments