CODEWITHSUNDEEP
mysqlsql
demi
demi

Reputation: 13

For each day list the best selling item in each category including total unit sold and revenue

I have a purchase table like below

customerID orderID  item   category     purDate   sold price
    1        001    book   stationary   1/1/2018    1   5
    2        002    ball    toys        1/1/2018    2   2
    3        003    shirt   cloth       1/1/2018    1   10
    1        004    pen    stationary   1/1/2018    5   3
    4        005    shirt   cloth       1/1/2018    2   10
    5        006    card    stationary  1/2/2018    15  2
    6        007    tshirt  cloth       1/2/2018    3   7
    2        008    book    stationary  1/3/2018    6   5
    3        009    car      toys       1/3/2018    2   4
    1        010    book    stationary  1/3/2018    4   5
    4        011    ball     toys       1/4/2018    2   2
    6        012    pen     stationary  1/4/2018    2   3
    7        013    notebk  stationary  1/4/2018    2   3

and I would like to query the best selling item for each day and category using MySQL so the query result would look like

purDate     category    item   total revenue
1/1/2018    stationary  pen       5     15
1/1/2018     toys       ball      2      4
1/1/2018     cloth      shirt     3     30
1/2/2018    stationary  card     15     30
1/2/2018     cloth      tshirt    3     21
1/3/2018    stationary  book     10     50
1/3/2018     toys       car       2      8
1/4/2018     toys       ball      2      4
1/4/2018    stationary  pen       2      6
1/4/2018    stationary  notebk    2      6

I've checked other related topics on stackoverflow but couldn't find what I'm looking for. I am trying to use window function with aggregate functions, but keep failing.

SELECT purDate, category, item, total, revenue 
 FROM (SELECT purDate, category, item, SUM(sold) AS total, sold * price AS revenue, 
   RANK() OVER(PARTITION BY purDate, category ORDER BY SUM(sold) DESC) AS rank 
 GROUP BY 1,2,3) q
 WHERE q.rank = 1

Thank you for your help in advance.

Upvotes: 0

Views: 66

Answers (2)

sticky bit
sticky bit

Reputation: 37507

You miss a FROM in your inner query. And you missed to also group by price and to build the revenue as sum(sold) * price. Other than that you were absolutely on the right path.

SELECT purdate,
       category,
       item,
       total,
       revenue
       FROM (SELECT purdate,
                    category,
                    item,
                    sum(sold) total,
                    sum(sold) * price revenue,
                    rank() OVER (PARTITION BY purdate
                                 ORDER BY sum(sold) DESC) r
                    FROM purchase
                    GROUP BY purdate,
                             category,
                             item,
                             price) x
       WHERE r = 1
       ORDER BY purdate;

db<>fiddle

Upvotes: 2

Bleach
Bleach

Reputation: 561

Dont be afraid to break what you are trying to accomplish into multiple steps instead of one gigantic query. 

Create table bleach select 
pur_date, max(sold) as max_sold, 
'' as total_rev
from your_table group by 
pur_date;

Alter table bleach add 
index(pur_date);

Alter table bleach add index 
(max_sold);

Select a.* from your_table a, 
bleach b where 
a.pur_date=b.pur_date and 
a.max_sold=b.max_sold;

Update bleach set 
total_rev=sold*price

Drop table bleach;

Upvotes: 1

Related Questions