How to count groups of rows and display the top/bottom 3

Question

I realize this is likely 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 descending (or ascending) order. I'm just not entirely sure how to construct a query like that.

Answer 1

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

Answer 2

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.