How would you filter products using both order-line count and total units?

Instruction: Using PostgreSQL, return products with at least two order lines and at least five units in total, ordered by product.

Context:

The table is order_lines(product TEXT NOT NULL, quantity INTEGER NOT NULL). Rows are (A,2), (A,3), (B,4), (C,5), (C,5). Return product and total units. Each row is one order line, including identical rows.

Updated

Official Answer

SELECT product, SUM(quantity) AS units
FROM order_lines
GROUP BY product
HAVING COUNT(*) >= 2 AND SUM(quantity) >= 5
ORDER BY product;

The supplied rows produce A, 5 and C, 10. B has only one line and is excluded. Both conditions describe each product group, so they belong in HAVING.

COUNT(*) counts rows, not units. Replacing it with SUM(quantity) would no longer enforce the requirement for two order lines. A WHERE quantity >= 5 filter would remove individual lines before grouping and incorrectly discard A.

Source

PostgreSQL: SELECT and HAVING.

Related Questions