List:General Discussion« Previous MessageNext Message »
From:mos Date:May 8 2011 5:21pm
Subject:How to find top 25 selling products for each day of year?
View as plain text  
I have a table (MyISAM) with summarized Sales data:

Table: ProdSales
Columns:
Sales_Date   Date,
Product_Code Char(10),
Amt_Sold  Double


There are approx 5,000 products sold each day and there are 3 years worth 
of data. I would like to create a table with the top 25 Amt_Sold products 
for each day

Example:

'2011-03-01', "ABC001", 30421.21
'2011-03-01', "ABC031", 30000.15
'2011-03-01', "ABC011", 23312.00
'2011-03-01', "ABC101", 22211.87
'2011-03-01', "DE0211", 21931.44
'2011-03-01', "AGC331", 20321.32
'2011-03-01', "DEF321", 20300.31
'2011-03-01', "KLC031", 20000.21
'2011-03-01', "MIU031", 19332.00
....
25th top Amt_Sold for 2011-03-11

'2011-03-02', "FER001", 40421.21
'2011-03-02', "DEC031", 40010.15
etc..


So the table would have 25 rows per date and there would be several years 
worth of data.

Now I can do this easy enough for one date. But is there an efficient way 
of doing this for each day of the year without resorting to executing the 
same SQL statement for each day?

TIA
Mike
(MySQL 5.5)

Thread
How to find top 25 selling products for each day of year?mos8 May
  • Re: How to find top 25 selling products for each day of year?Peter Brawley8 May