CODEWITHSUNDEEP
mysqlsql
sqlnewbie
sqlnewbie

Reputation: 19

Display N most sold items per day

I am clueless how can I write a (MySQL) query for this. I am sure it is super simple for an experienced person.

I have a table which summarizes sold items per day, like:

date item quantity
2020-01-15 apple 3
2020-01-15 pear 2
2020-01-15 potato 1
2020-01-14 orange 3
2020-01-14 apple 2
2020-01-14 potato 2
2020-01-13 lemon 5
2020-01-13 kiwi 2
2020-01-13 apple 1

I would like to query the N top sellers for every day, grouped by the date DESC, sorted by date and then quantity DESC, for N = 2 the result would look like:

date item quantity
2020-01-15 apple 3
2020-01-15 pear 2
2020-01-14 orange 3
2020-01-14 apple 2
2020-01-13 lemon 5
2020-01-13 kiwi 2

Please tell me how can I limit the returned item count per date.

Upvotes: 1

Views: 805

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you can use:

select t.*
from t
where (quantity, item) >= (select t2.quantity, t2.item
                           from t t2
                           where t2.date = t.date
                           order by t2.quantity desc, t2.item
                           limit 1 offset 1
                          );

The only caveat is that you need to have at least "n" items available on the day (although that condition can be added as well).

Upvotes: 0

Wynn Teo
Wynn Teo

Reputation: 190

You can do this if you are using MySQL 8.0++

SELECT * FROM 
 (SELECT DATE, ITEM, QUANTITY, ROW_NUMBER() OVER (PARTITION BY DATE ORDER BY QUANTITY DESC) as order_rank FROM TABLE_NAME) as R
WHERE order_rank < 2

Upvotes: 0

TimLer
TimLer

Reputation: 1360

First of all, it is not a good idea to use DATE as the name of a column.

You can use @rank := IF(@current = date, @rank + 1, 1) to number your rows by DATE. This statement checks each time that if the date has changed, it starts counting from zero.

Select date, item, quantity
from
(
     SELECT   item, date, sum(quantity) as quantity,
              @rank := IF(@current = date, @rank + 1, 1) as ranking,  
              @current := date
     FROM     yourtable
     GROUP BY item, date
     order by date, sum(quantity) desc
) t
where t.ranking < 3

Upvotes: 1

Related Questions