Format to show Query response in MySQL

Question

Format to show Query response in MySQL

Navigation

#1 by (2 votes)

3

I would like to know if there is a way to get the last two weeks and its amounts in SQL, I explain:

The following query brings me the last two weeks with its quantities of the same product:

$first        = strtotime('last Monday -14 days');
$last         = strtotime('next Sunday -7 days');
$dateInitial  = date('Y-m-d', $first);
$dateFinal    = date('Y-m-d', $last);

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 ".$dateInitial." AND ".$dateFinal."
       AND pf_harvest.id_tenant = 1
       AND pf_harvest.id_product = 1
GROUP BY product, WEEKOFYEAR(pf_harvest.date)";

The result you give me is the following:

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

That's fine, the problem is that I want the result of the query to leave something like this:

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

I pass the dates in the following way:

This in order to obtain the quantities of both week 33 and 34 grouped in the same product. I do not know if it's possible to do it this way, what I do not want is to play with Javascript to show the results.

mysql sql php

asked by Fabian Sierra 01.09.2016 в 14:54

source

1 answer

How to create an online multiplayer game [closed] Can I use open source code from another developer for commercial use?

score 2 · Accepted Answer

You can try using your current query as a derived table, and doing a new aggregation on it:

"SELECT T.product,
       MAX(CASE WHEN T.week = WEEKOFYEAR(".$dateInitial.") THEN quantity END) week1,
       MAX(CASE WHEN T.week = WEEKOFYEAR(".$dateFinal.") THEN quantity END) week2
FROM (  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 ".$dateInitial." AND ".$dateFinal."
               AND pf_harvest.id_tenant = 1
               AND pf_harvest.id_product = 1
        GROUP BY product, WEEKOFYEAR(pf_harvest.date)) AS T
GROUP BY T.product";