Give ChatGPT the database dialect, actual column names, relevant keys, and permitted sample rows. OpenAI's prompting advice emphasizes clear context and iteration; for SQL, the useful feedback is an exact query and a result that differs from the expected rows. Official prompting guidance
Define the result before the join
This original fictional report needs one row per customer, with total order value and the number of line records. It uses two small source tables:
| order_id | customer_id | total |
|---|---|---|
| 101 | A | 20 |
| 102 | A | 20 |
| line_id | order_id |
|---|---|
| 1 | 101 |
| 2 | 101 |
| 3 | 102 |
The expected answer for customer A is total order value 40, with 3 line records. The orders happen to have equal values. Keeping that detail tests whether a proposed fix incorrectly treats repeated amounts as duplicate orders.
A direct join repeats order 101's total for each of its two lines. Summing after that join gives 20 + 20 + 20 = 60. Changing the expression to SUM(DISTINCT total) gives 20 because the two orders have the same value. Neither result answers the question.
Ask for the query and the reason each row counts once
Write a PostgreSQL SELECT query for this report.
Output grain: one row per customer_id.
Required measures: total order value counted once per order_id,
and number of line records across those orders.
Schema:
orders(order_id primary key, customer_id, total)
order_lines(line_id primary key, order_id references orders.order_id)
Fictional rows:
orders: (101, A, 20), (102, A, 20)
order_lines: (1,101), (2,101), (3,102)
Expected result: A, total 40, line count 3.
Orders with no lines must remain in the total with a zero line count.
Return the SELECT query, the expected fixture result, and a brief
explanation of how you avoid multiplying order totals. Do not use
SUM(DISTINCT total) as a substitute for order identity. Use only the
listed schema. Do not invent columns, run changes, or propose writes.
Name any assumptions that require clarification before real use.
Aggregate the many-side records before joining
For this schema, count line records per order first. Each order then joins to at most one count row, so its total is added once:
WITH line_counts AS (
SELECT order_id, COUNT(*) AS line_count
FROM order_lines
GROUP BY order_id
)
SELECT
o.customer_id,
SUM(o.total) AS order_total,
SUM(COALESCE(l.line_count, 0)) AS line_count
FROM orders AS o
LEFT JOIN line_counts AS l
ON l.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;
The common table expression gives a name to the intermediate count query. The LEFT JOIN retains an order without line records; COALESCE supplies zero for its missing count. PostgreSQL documents WITH queries and the behavior of aggregate functions, including how SUM and COUNT treat inputs.
The fixture's expected row is A | 40 | 3. Add a third order, (103, A, 7), with no lines: the expected row becomes A | 47 | 3. That second case tests the requirement to preserve orders without line records. These are expected results calculated from the fictional data, not observations from your database.
Test the business rules your data actually uses
Run the proposed SELECT in an approved development or read-only reporting environment against representative data. Check a few order IDs directly. Confirm whether total includes canceled orders, refunds, tax, or mixed currencies; the fictional schema contains none of those rules.
If a result is wrong, return the smallest rows that reproduce it and the exact output you observed. Asking “make the SQL correct” leaves the reporting policy unstated. Asking “order 103 has no line records and is missing from the total” gives ChatGPT a specific requirement to repair.
For a different LEFT JOIN problem, compare filters in ON and WHERE. For questions about whether identifiers are unique individually or as a combination, read the declared keys before using them to count business entities.
Keep the accepted query, the fixture, and the reporting definitions together. When someone adds another table to the report, rerun the small case with two equal-valued orders; it is a quick way to catch a total that looks reasonable while counting the wrong thing.