How to count groups of rows and display the top/bottom 3
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.
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
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.
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
In MySQL
:
SELECT item_id, COUNT(*) AS cnt
FROM orders
GROUP BY
item_id
ORDER BY
cnt DESC
LIMIT 3
精彩评论