Assume the following schema:
Assume that we want to generate the raw data for creating a histogram of the number of songs per genre. To be specific, we are looking for the following output:n | freq |
---|---|
1 | 51 |
2 | 1 |
3 | 1 |
4 | 0 |
5 | 0 |
You can read the above as follows:
Limit your response to five rows.
WITH recursive songs_per_genre AS
(
SELECT genre, COUNT(DISTINCT trackid) as songs
FROM songs
WHERE genre != '-1'
GROUP BY genre
), freq_table as
(
SELECT (SELECT MIN(songs) FROM songs_per_genre) AS n
UNION ALL
SELECT n+1 FROM freq_table WHERE n < (SELECT MAX(songs) FROM songs_per_genre)
)
SELECT n, COUNT(songs) as freq
FROM freq_table LEFT JOIN songs_per_genre
ON n=songs
GROUP BY n
LIMIT 5;
n | freq |
---|---|
1 | 51 |
2 | 1 |
3 | 1 |
4 | 0 |
5 | 0 |