CODEWITHSUNDEEP
mysqlsql
Ardor Orenda
Ardor Orenda

Reputation: 51

Group by Count output in MySQL?

I have a question about combining the COUNT and GROUP BY queries (new at MySQL).

+---------+------+
| Visitor | Shop |
+---------+------+
| Mark    | aaa  |
| Mark    | aaa  |
| Mark    | bbb  |
| Mark    | ccc  |
| Jane    | aaa  |
| Jane    | bbb  |
| Cody    | aaa  |
| Cody    | aaa  |
| Cody    | bbb  |
+---------+------+

I used this query to find the number of distinct shops each visitor visited:

SELECT Visitor, COUNT(DISTINCT Shop) AS unique_shops
FROM my_table
GROUP BY Visitor
ORDER BY unique_shops DESC

It worked fine, but now I want to do the reverse and count how many people visited the same number of distinct shops (how many visited 1, 2, 3 etc distinct shops).

+---------+-------- +
| Shops   | Visitors|
+---------+---------+
| 2       | 2       |
| 3       | 1       |
+---------+---------+

I thought I could do it by Grouping By unique_shops variable, but MySQL Workbench shows an error. I was then thinking of storing the unique_shops as a separate column and then somehow matching the Names to that... But there should probably be a more straightforward approach. Thank you.

Upvotes: 3

Views: 127

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use one more level of aggregation:

SELECT unique_shops, COUNT(*) as num_visitors
FROM (SELECT Visitor, COUNT(DISTINCT Shop) AS unique_shops
      FROM my_table
      GROUP BY Visitor
     ) v
GROUP BY unique_shops
ORDER BY unique_shops DESC;

Upvotes: 2

Related Questions