Gathering the items by category

01 / 04

The items table holds three numbers

items has two amounts of money in it. price is what it sells for, cost is what it was bought in for. stock is how many are left.

Those three let you say "which is dear", "which earns" and "which is piling up". Start with the whole.

SELECT COUNT(*) AS products,       AVG(price) AS average_price  FROM items;

Result

10 | 1045

02 / 04

Gather by category

Item by item that is ten rows. Gather by category and it becomes four, and the tendency comes forward instead.

"Per category" means GROUP BY category.

SELECT category,       COUNT(*) AS products  FROM items  GROUP BY category;

Result

cleaning | 2
interior | 1
kitchen | 4
stationery | 3

03 / 04

A total and an average say different things

SUM is how much has piled up, AVG is how big one of them is. The same column shows a different view.

For stock you usually take SUM — "how much are we holding" — and for price AVG — "what price bracket is this".

SELECT category,       SUM(stock) AS total_stock,       AVG(price) AS average_price  FROM items  GROUP BY category;

Result

cleaning | 102 | 1100
interior | 6 | 2400
kitchen | 148 | 900
stationery | 234 | 750

04 / 04

Watch out for the thin groups

Interior holds a single item. Take an average of it and all you get back is that one item's price.

When you read an average, look at how many it is an average of at the same time. That is why keeping COUNT(*) alongside stops you misreading. Let us write some.