Fabian Sierra Fabian Sierra - 3 months ago 12
MySQL Question

Get the last two weeks SQL

I wonder if there is a way to get the last two weeks and their amounts in SQL , I explain:

The following query brings me the last two weeks with their quantities of a product in this way :

SELECT pr_products.product,
WEEKOFYEAR(pf_harvest.date) AS week,
SUM(pf_harvest.quantity) AS quantity
FROM pf_harvest
INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
WHERE pf_harvest.date BETWEEN '2016-08-15' AND '2016-08-28'
AND pf_harvest.id_tenant = 1
AND pf_harvest.id_product = 1
GROUP BY product, WEEKOFYEAR(pf_harvest.date);


The result I get is the following:

-------------------------------------
product | week | quantity |
-------------------------------------
ROSA PINK | 33 | 1076000 |
-------------------------------------
ROSA PINK | 34 | 1094025 |
-------------------------------------


That is fine, the problem is that I want the query result left me something like this:

-------------------------------------
product | week1 | week2 |
-------------------------------------
ROSA PINK | 1076000 | 1094025 |
-------------------------------------


This is to obtain the quantities of both week 33 and the 34 grouped in a single product , I donĀ“t know if it is possible do it this way, Thank you!

Answer

you should use conditional aggergation. each column is aggregating only its week. so one query with one pass will get you both aggregations.

SELECT pr_products.product,
       SUM(case when WEEKOFYEAR(pf_harvest.date)=43 then pf_harvest.quantity end) week1, 
       SUM(case when WEEKOFYEAR(pf_harvest.date)=44 then pf_harvest.quantity end) week2
FROM pf_harvest
   INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
WHERE pf_harvest.date BETWEEN '2016-08-15' AND '2016-08-28'
 AND pf_harvest.id_tenant = 1
 AND pf_harvest.id_product = 1
GROUP BY product