How to get multiple counts from one SQL query

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.