Build a query one step at a time

This tutorial answers one question: how many orders belong to each user, including users who have no orders? Build the answer in stages so you can check the join before trusting the total.

The SQL uses the documentation's PostgreSQL demo schema. Replace test_schema, users, orders, and their fields with your own names. Open Scratchpad after connecting to the database.

Check the keys#

Use Info to inspect users.id and orders.user_id. Graph can show their declared foreign-key relationship. Read a small sample from both tables to check that the values match the relationship you intend to count.

Inspect the joined records#

Run this query in a cell:

SELECT u.id, u.username, o.id AS order_id
FROM test_schema.users AS u
LEFT JOIN test_schema.orders AS o ON o.user_id = u.id
ORDER BY u.id, o.id
LIMIT 25;

A user with several orders appears several times. A user with no orders still appears, with a missing order_id. The LEFT JOIN keeps that user in the result, which is required by the question.

Check a user whose orders you know. If the join produces unexpected duplicates, resolve them before adding an aggregate.

Count in a separate cell#

Keep the sample query and add a new cell:

SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM test_schema.users AS u
LEFT JOIN test_schema.orders AS o ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY order_count DESC, u.id;

COUNT(o.id) counts matching orders. COUNT(*) would also count the joined row kept for a user with no orders, giving that user a count of one. Grouping by both user ID and name also avoids merging different users who happen to share a name.

Verify the meaning of the total#

Compare one user's count with a direct lookup in orders. Check a user with no orders as well. This query counts all orders; it does not exclude cancelled orders or apply a date range. Add those rules only after deciding what the report should mean.

Save the SQL with that definition. To share the answer, follow Export an answer for analysis.