SQL - how to count groups of rows and display the top/bottom 3

Question

I realise this is likey to be an easy one, but my SQL is basic at best.

Lets say I have a table containing a list of orders, with 'item_id' being one of the columns. I need to display the 3 least (or 3 most) popular orders of item.

I know that I need to group the orders using item_id and then count them. Then I need to display the bottom (or top) 3 in decsending (or ascending) order. I'm just not entirely sure how to construct a query like that.

Any help would be appreciated. Thanks

Answer 1

select item_id, count(*)
from table
group by item_id;

will give you the whole list.

Bottom 3:

select item_id
from (
    select item_id, count(*) as cnt
    from table
    group by item_id
    order by cnt
) where rownum < 4;

Top 3:

select item_id
from (
    select item_id, count(*) as cnt
    from table
    group by item_id
    order by cnt desc
) where rownum < 4;

NOTE: this sytnax is for Oracle. Use LIMIT if you have MySql or TOP if you have sql-server.

ORDER BY will sort your results. Ascending order is default, so use 'desc' if you want to get the biggest.

GROUP BY (when used with count(*)) will count groups of similar objects

where rownum < 4: This is oracles was of doing a LIMIT. It returns the first 3 rows of the query that has been run. Where clauses are run before order clauses, so that is why you have to do this as a nested query.

Answer 2

In MySQL:

SELECT  item_id, COUNT(*) AS cnt
FROM    orders
GROUP BY
        item_id
ORDER BY
        cnt DESC
LIMIT 3

Answer 3

For SQL server:

select    top 3 item_id, count(item_id) 
from      table 
group by  item_id 
order by  count(item_id) desc

will give you the 3 most popular item_id's

using:

order by  count(item_id) 

will give you the 3 least popular item_id's

Answer 4

In Sql Server:

SELECT TOP 3 item_id, COUNT(*) as itemcount FROM table GROUP BY item_id ORDER BY itemcount

And add DESC to order descent