Comparing with conditions attached

01 / 04

Narrow, then count

"How many items of 1000 or more are there in each category?" narrows the rows first, then gathers. WHERE bites before GROUP BY.

The writing order is fixed the same way: WHERE then GROUP BY.

SELECT category,       COUNT(*) AS products  FROM items  WHERE price >= 1000  GROUP BY category;

Result

cleaning | 1
interior | 1
kitchen | 2
stationery | 1

02 / 04

"The categories with two or more items", on the other hand, narrows on the number after gathering. That is HAVING's job.

There is one way to tell them apart. If the condition uses an aggregated value, it is HAVING; if it uses a value from one row, it is WHERE.

SELECT category,       COUNT(*) AS products  FROM items  GROUP BY category  HAVING COUNT(*) >= 2;

Result

cleaning | 2
kitchen | 4
stationery | 3

03 / 04

Take the cost away from the price and you have what one of them earns. SUM and AVG work on a calculated result as well.

A calculated column reads badly without a name, so give it one with AS.

SELECT category,       AVG(price - cost) AS margin  FROM items  GROUP BY category;

Result

cleaning | 575
interior | 1200
kitchen | 425
stationery | 380

04 / 04

You can line up several aggregations in one query. Looking at the same groups through different measures gives you more to decide with.

A finding like "plenty of items, but each one earns little" comes out of this shape. Let us write some.

SELECT category,       COUNT(*) AS products,       AVG(price) AS average_price,       AVG(price - cost) AS margin  FROM items  GROUP BY category;

Result

cleaning | 2 | 1100 | 575
interior | 1 | 2400 | 1200
kitchen | 4 | 900 | 425
stationery | 3 | 750 | 380