-1

I am not very familiar with sql however I have to do a query for a sale prediction.

The data is for the sale with distinct prodID, shopID, weekDay, date and Sale. I need to get the sale of the same product in the same shop and same weekDay in the past (e.g) 3 weeks. Therefore some proper pivoting is necessary. There might be that for some days there is no sale record for that product-shop-weekday combination. Also importantly I have to ignore negative sale values (if any) for average calculation.

This is header of the data:

DATE        prodID  shopID  sale    weekDay
2017-03-01  8       16      4.8     Wednesday
2017-03-01  2       16      18.8    Wednesday
2017-03-01  62      16      1.7     Wednesday
2017-03-01  34      16      3.6     Wednesday
2017-03-01  32      16      12.0    Wednesday
2017-03-02  8       16      3.6     Thursday
2017-03-02  34      16      15.8    Thursday

Ideal outcome is:

DATE  prodID    shopID   sale  weekDay   saleWeek-1 saleWeek-2 saleWeek-3 ave_3sale

Perhaps a self-join can be used to build the new columns. Thank you very much much for your help.

physiker
  • 889
  • 3
  • 16
  • 30
  • See [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) to also provide the expected results. – Raymond Nijland May 19 '19 at 12:46
  • saleseek -1 is build like this( SELECT Sum(sale),shopID as sales1 FROM Salestable WHERE between DATE- interval -7 day and DATE() GROUP BY shopID) as saleseek-1 and you make that the same for all weeks. Then you join them all with on orgtable.shopID = saleseek-1.shopID and so on. try it and if you find problems – nbk May 19 '19 at 14:38
  • @nbk could you please write it more clear with respect to my column-names? thank you – physiker May 19 '19 at 17:15

1 Answers1

1

I made you an sqlfiddle example to show you how you can make your sql statement http://www.sqlfiddle.com/#!9/1877b3/4/0 You of course put in your tablename and columnnames. the example would show you the sales of every day plus what you sold of that product for that salesID last week and that 2 weeks before.

CREATE TABLE salestable (
 id MEDIUMINT NOT NULL  AUTO_INCREMENT,
datelit Date NOT NULL,
  productID int(10) NOT NULL,
  saleID int(10) NOT NULL,
 sale float(4,2) NOT NULL,
   weekday CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;
INSERT INTO `salestable` (`id`,`datelit`, `productID`, `saleID`,`sale`,`weekday`) VALUES
(NULL,'2019-05-18',  8,       16,      4.8,     'Wednesday'),
(NULL,'2019-05-18', 2,       16,      18.8,     'Wednesday'),
(NULL,'2019-05-18', 62,       16,      1.7,     'Wednesday'),
(NULL,'2019-05-18',  34,       16,      3.6,     'Wednesday'),
(NULL,'2019-05-17',  32,       16,      12.0,     'Wednesday'),
(NULL,'2019-05-18',  8,       16,      3.6,     'Wednesday'),
(NULL,'2019-05-18',  34,       16,      15.8,     'Wednesday');
SELECT a.datelit,a.productID, a.saleID, a.sale,a.weekday, b.salesumweek1, c.salesumweek2
FROM `salestable` a
Left JOIN (
SELECT saleID,productID, SUM(sale) as salesumweek1 
FROM `salestable`

  Where datelit BETWEEN  DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND CURDATE()
  GROUP BY saleID,productID
) b ON a.saleID = b.saleID AND a.productID = b.productID
Left JOIN (
SELECT saleID,productID, SUM(sale) as salesumweek2 
FROM `salestable`
Where datelit BETWEEN  DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND DATE_ADD(CURDATE(), INTERVAL -14 DAY)
  GROUP BY saleID,productID
) c ON a.saleID = c.saleID AND a.productID = c.productID;
nbk
  • 45,398
  • 8
  • 30
  • 47