Aggregating across a join in SQL

Group after joining to summarize the child rows per parent.

SELECT
    p.name AS product,
    COUNT(DISTINCT i.order_id) AS orders,
    SUM(i.quantity) AS units,
    ROUND(SUM(i.quantity * p.price), 2) AS revenue
FROM products AS p
JOIN order_items AS i
    ON i.sku = p.sku
GROUP BY p.name
HAVING SUM(i.quantity) > 10
ORDER BY revenue DESC;

How it works

1. The parent columns go in the GROUP BY, the child columns in aggregates.
2. COUNT(DISTINCT ...) avoids double counting fanned-out rows.
3. HAVING then filters on the aggregate.

Keywords and builtins used here

• AS
• BY
• COUNT
• DESC
• DISTINCT
• FROM
• GROUP
• HAVING
• JOIN
• ON
• ORDER
• SELECT
• SUM

The run, in numbers

Lines

11

Characters to type

261

Tokens

78

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 55 seconds.

Type this snippet

Step 9 of 9 in Joins, step 17 of 22 in Joins & aggregation.

← Previous Next →