Counting members by attribute

01 / 04

Turn it into something countable

"What sort of people are the members?" gets you nowhere as it stands. Turn it into a countable question first.

Every one of those is now something COUNT and AVG can answer.

02 / 04

When you hear "per something", that something is the column in your GROUP BY. Per area is area, per plan is plan.

Give the aggregated column a name with AS. It reads better, and ORDER BY can use that name too.

SELECT area, COUNT(*) AS people  FROM members  GROUP BY area;

Result

Central Town | 4
East Town | 1
North Town | 5
South Town | 2

03 / 04

To see it with the most people first, just sort on the aggregated column. The name you gave with AS goes straight into ORDER BY.

When two groups have the same number, nothing decides their order. In that case add a second sort key.

SELECT area, COUNT(*) AS people  FROM members  GROUP BY area  ORDER BY people DESC;

Result

North Town | 5
Central Town | 4
South Town | 2
East Town | 1

04 / 04

AVG and MAX take the same shape. Add a GROUP BY and you get the average or the largest per group.

You may line up as many aggregations in one query as you like. It only makes the result wider. Let us write some.

SELECT plan,       COUNT(*) AS people,       AVG(age) AS average_age  FROM members  GROUP BY plan;

Result

premium | 5 | 40
standard | 7 | 28