There are different methods for fetching multiple counts in SQL. One is to use subqueries to fetch individual counts:
SELECT (SELECT COUNT(*) FROM tracks t, genres g WHERE genre_id = g.id AND g.name = 'Blues') AS blues_count, (SELECT COUNT(*) FROM tracks t, genres g WHERE genre_id = g.id AND g.name = 'Rock') AS rock_count;
However this way isn’t very efficient. Here is a better alternative:
SELECT SUM(CASE WHEN g.name = 'Blues' THEN 1 ELSE 0 END) AS blues_count, SUM(CASE WHEN g.name = 'Rock' THEN 1 ELSE 0 END) AS rock_count FROM tracks t INNER JOIN genres g ON g.id = t.genre_id;
This method performs better. Analyzing the first option shows that the cost is higher, and multiple table scans are performed.
The second option results in a lower cost, fewer scans, and shorter execution time.