Aggregating group by group

01 / 05

Running it three times is odd

Last lesson, getting a total per stall meant naming one stall in the WHERE. Three stalls, three runs. A hundred stalls, a hundred runs.

That is silly, isn't it. There is a way to get them all in one go.

02 / 05

Add GROUP BY column and rows with the same value in that column become one bundle, with the aggregating done per bundle.

Read it as "bundle by stall, and total the sold in each". Three stalls' totals out of one run.

SELECT stall, SUM(sold) FROM sales  GROUP BY stall;

Result

Komorebi Studio | 18
Tsumugi Goods | 24
Yurari Workshop | 6

03 / 05

With aggregate functions alone you got one row. Add GROUP BY and you get as many rows as there are groups.

And in SELECT you may line up the columns you named in GROUP BY alongside the aggregate functions. Those two kinds are safe to mix.

SELECT stall, COUNT(*) FROM sales  GROUP BY stall;

Result

Komorebi Studio | 3
Tsumugi Goods | 3
Yurari Workshop | 2

04 / 05

This is not just a SUM thing. AVG, COUNT, MAX and MIN are all worked out per group in the same way.

Below is the average price per stall. Now you can see how dear each range is at a glance.

SELECT stall, AVG(price) FROM sales  GROUP BY stall;

Result

Komorebi Studio | 700
Tsumugi Goods | 1100
Yurari Workshop | 1800

05 / 05

Put an aggregate function in ORDER BY and you can sort by the aggregated result. Most sold first, and it is a ranking.

The writing order is FROM → GROUP BY → ORDER BY. Let us write some.

SELECT stall, SUM(sold) FROM sales  GROUP BY stall  ORDER BY SUM(sold) DESC;

Result

Tsumugi Goods | 24
Komorebi Studio | 18
Yurari Workshop | 6