How to group the result of GROUP BY in a query?

Question

I want to SELECT 5 most sold articles.

SELECT ID, shop, article_nr, count(*) as count
FROM table
GROUP BY shop, article_nr
ORDER BY count DESC
LIMIT 5

Results of this query:

ID | shop   | article_nr | count
--------------------------------------
71 | amazon | 123        | 280
98 | amazon | 223        | 170
35 | amazon | 323        | 99
7  | ebay   | 456        | 71
11 | amazon | 789        | 49
4  | ebay   | 032        | 10

How to group this results by the column 'shop' in a second step (maybe edit the query)?

It should look like this (only 'one' shop in the top 5):

ID | shop       | article_nr  | count
--------------------------------------
71 | amazon     | 123         | 280
7  | ebay       | 456         | 71
.. | shop_x     | ...         | 55
.. | shop_y     | ...         | 40
.. | shop_z     | ...         | 37

Can someone help me?

if you had two articles for the same shop with the same count, do you want both, or if not, which one, earliest id, biggest article? — Tony Hopkinson, Sep 05 '13 at 17:17

score 1 · Answer 1 · answered Sep 05 '13 at 17:24

1

This is what you want

SELECT id, shop, article_nr, count FROM table n 
WHERE count = (SELECT MAX(count) FROM table GROUP BY shop HAVING shop = n.shop) LIMIT 5;

answered Sep 05 '13 at 17:24

Kapil Jain

188
1
9

It looks like you are making the assumption here that count is a column and not an aggregate result of the query. This would work if that was the case, but if you look at the original query you will note that the count column is from the initial grouping. – Matthew Sep 05 '13 at 20:21

score 0 · Accepted Answer · edited May 23 '17 at 11:54

0

Popular and well described answer to your problem is right here: https://stackoverflow.com/a/612268/1803682

Specifically how do I select AggregateFunction(column) distinct on (other column)

I will be happy to help more if you want to play with this in a SQL Fiddle and post it.

edited May 23 '17 at 11:54

Community

1
1

answered Sep 05 '13 at 17:03

Matthew

9,851
4
46
77

How to group the result of GROUP BY in a query?

2 Answers2