Count roots and children in one row
Your task
Return root_count and child_count. Roots have no parent_id, while children have a parent_id.
Conditional aggregates can put two related counts side by side in one row.
Sample data
categories| id | name | parent_id |
|---|---|---|
| 1 | Music | NULL |
| 2 | Jazz | 1 |
| 3 | Indie | 1 |
| 4 | Bebop | 2 |
| 5 | Cool Jazz | 2 |
| 6 | Merch | NULL |
| 7 | Shirts | 6 |
Expected output
| root_count | child_count |
|---|---|
| 2 | 5 |
Show a hint
Use CASE inside SUM for roots and COUNT(parent_id) for children.
Aggregates and reporting guide
Set the grain of a summary
Without GROUP BY, aggregates summarize the entire input. With GROUP BY genre, each result row represents one genre. Include a group key or an aggregate for each selected value so the meaning of the result is clear.
SELECT genre, COUNT(*) AS albums,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM albums
GROUP BY genre;Filter rows or filter groups
WHERE runs before grouping. HAVING tests the resulting groups. Here sold-out albums cannot contribute to the counts or averages. Genres with fewer than two qualifying albums are dropped after grouping.
SELECT genre, COUNT(*) AS in_stock, AVG(price) AS average_price
FROM albums
WHERE stock > 0
GROUP BY genre
HAVING COUNT(*) >= 2;Count records at the right level
A LEFT JOIN leaves one row with a NULL sale ID for each unsold album. COUNT(*) would count that placeholder. COUNT(s.id) skips it and correctly shows zero sales for those albums.
SELECT a.title, COUNT(s.id) AS sale_records
FROM albums AS a
LEFT JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id;Worked solution
SELECT SUM(CASE WHEN parent_id IS NULL THEN 1 ELSE 0 END) AS root_count, COUNT(parent_id) AS child_count FROM categories;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
