1

Here is my table

CREATE TABLE financials ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue INT NOT NULL, 
cost INT NOT NULL, 
reg_date TIMESTAMP 
); 


INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES 
( 1, 'Canada', 'Doe'   , 20,  5, '2010-01-31 12:01:01'), 
( 2, 'USA'   , 'Tyson' , 40, 15, '2010-02-14 12:01:01'), 
( 3, 'France', 'Keaton', 80, 25, '2010-03-25 12:01:01'),
( 4, 'France', 'Keaton',180, 45, '2010-04-24 12:01:01'),
( 5, 'France', 'Keaton', 30,  6, '2010-04-25 12:01:01'),
( 6, 'France', 'Emma'  , 15,  2, '2010-01-24 12:01:01'),
( 7, 'France', 'Emma'  , 60, 36, '2010-01-25 12:01:01'),
( 8, 'France', 'Lammy' ,130, 26, '2010-04-25 12:01:01'),
( 9, 'France', 'Louis' ,350, 12, '2010-04-25 12:01:01'), 
(10, 'France', 'Dennis',100,200, '2010-04-25 12:01:01'),
(11, 'USA'   , 'Zooey' , 70, 16, '2010-04-25 12:01:01'),
(12, 'France', 'Alex'  ,  2, 16, '2010-04-25 12:01:01');

For each product and date combination, I need to get the revenue for previous 5 days. For instance, for Product ‘Keaton’, the last purchase was on 2010-04-25, it will only sum up revenue between 2010-04-20 to 2010-04-25 and therefore it will be 210. While for "Emma", it would return 75, since it would sum everything between 2010-01-20 to 2010-01-25. 

SELECT ProductID, sum(revenue), reg_date
  FROM financials f
Where reg_date in (
    SELECT reg_date
    FROM financials as t2
    WHERE t2.ProductID = f.productID
    ORDER BY reg_date
    LIMIT 5)

Unfortunately, when i use either https://sqltest.net/ or http://sqlfiddle.com/ it says that 'LIMIT & IN/ALL/ANY/SOME subquery' is not supported. Would my query work or not?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
EdwardC
  • 103
  • 9
  • I changed the data type of cost and revenue because... well it's obvious, isn't it? Anyway, please provide the desired result – Strawberry Jan 11 '18 at 22:05

2 Answers2

1

Your query is on the right track, but probably won't work in MySQL. MySQL has limitations on the use of in and limit with subqueries.

Instead:

SELECT f.ProductID, SUM(f.revenue)
FROM financials f JOIN
     (SELECT ProductId, MAX(reg_date) as max_reg_date
      FROM financials
      GROUP BY ProductId
     ) ff
     ON f.ProductId = ff.ProductId and
        f.reg_date >= ff.max_reg_date - interval 5 day
GROUP BY f.ProductId;

EDIT:

If you want this for each product and date combination, then you can use a self join or correlated subquery:

SELECT f.*,
       (SELECT SUM(f2.revenue)
        FROM financials f2
        WHERE f2.ProductId = f.ProductId AND
              f2.reg_date <= f.reg_date AND
              f2.reg_date >= f.reg_date - interval 5 day
       ) as sum_five_preceding_days
FROM financials f;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

After some trials I ended up with some complex query, that I think it solves your problem

SELECT
  financials.ProductID, sum(financials.Revenue) as Revenues
FROM
  financials 
INNER JOIN (
      SELECT ProductId, GROUP_CONCAT(id ORDER BY reg_date DESC) groupedIds
      FROM financials
      group by ProductId
) group_max
  ON financials.ProductId = group_max.ProductId
     AND FIND_IN_SET(financials.id, groupedIds) BETWEEN 1 AND 5
group by financials.ProductID

First I used group by financials.ProductID to count revenues by products. The real problem you are facing is eliminating all rows that are not in the top 5, for each group. For that I used the solution from this question, GROUP_CONCAT and FIND_IN_SET, to get the top 5 result without LIMIT. Instead of WHERE IN I used JOIN but with this, WHERE IN might also work.

Heres the FIDDLE

Máthé Endre-Botond
  • 4,826
  • 2
  • 29
  • 48