Cheat Sheet logoCheat Sheet Today
SQLite Joins, CTEs, and Window Functions Cheatsheet/Join and aggregate

Count child rows per parent.

Back to sheetBrowse commands
Sign in to save
Section: Join tables

Join and aggregate

sql
sql
SELECT u.id, u.email, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.email;
Explanation

A practical dashboard query pattern.

View in sheet →

Learn the surrounding workflow

Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.

Compare commandsView fixes

Related commands

Same sheet · prioritizing Join tables
Use an INNER JOIN
Return rows that match in both tables.
OpenIn sheetsqlsame section
Use a LEFT JOIN
Keep parent rows even if the child is missing.
OpenIn sheetsqlsame section
Use a CTE to stage a subquery
Name an intermediate result set.
OpenIn sheetsql1 tag match
Use a recursive CTE
Walk a hierarchy such as categories or folders.
OpenIn sheetsql1 tag match
Assign row numbers
Rank rows within an ordered result.
OpenIn sheetsql1 tag match
Compute a running total
Use SUM() OVER for cumulative metrics.
OpenIn sheetsql1 tag match