Mysql – Monthly grouped data

MySQLPHPpivot

OK! I've a table that looks like this: SQLFiddle

And I want to get some handlers to provide to my renderer php page, so that the final result be like this: 

+----------------+----------------------------+----------------------------+
|                |           JANUARY          |           FEBRUARY         |
+  product_name  +---------------+------------+---------------+------------+
|                | Tt. quant_ton | AVG  value | Tt. quant_ton | AVG  value |
+----------------+---------------+------------+---------------+------------+
|     gravel     |       41      |   51.25    |      22       |   55.00    |
+----------------+---------------+------- ----+---------------+------- ----+
|      soil      |       23      |   29.90    |      10       |   13.00    |
+----------------+---------------+------------+---------------+------------+

I don't know how to retrieve this data. I tried with CASE THEN but one case removes data from the other, leaving the table with blank fields.

Best Answer

You can use your CASE variant with some modifications. First you need to extend the GROUP BY with MONTH(date): 

SELECT product_name,
  CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
  CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)

If not MySQL will randomly pick one month for you. There's a lot to be said about this default setting (insane comes to mind:-), but look up sql_mode ONLY_FULL_GROUP_BY for ways around it.

As you can see now you will get null for non-matching months:

product_name    JAN    FEV
gravel          41     (null)
gravel          (null) 22
soil            23     (null)
soil            (null) 10

By picking - for example - max of JAN and FEV you will get rid of those

SELECT product_name, max(JAN), max(FEV)
FROM (
    SELECT product_name,
        CASE WHEN MONTH(date) = 1 THEN SUM(quant_ton) END AS JAN,
        CASE WHEN MONTH(date) = 2 THEN SUM(quant_ton) END AS FEV
    FROM tb_import
    JOIN tb_cost
        ON tb_import.productid = tb_cost.productid
    JOIN tb_sites
        ON tb_sites.site_num = tb_import.site_cd
    JOIN tb_product
        ON tb_product.prod_id = tb_import.productid
    GROUP BY product_name, MONTH(date)
) as t
GROUP BY product_name;

product_name    max(JAN)    max(FEV)
gravel          41          22
soil            23          10

However, the pivoting is only a presentation of the result. I would choose a query like:

SELECT product_name,
       MONTH(date),
       SUM(quant_ton) 
FROM tb_import
JOIN tb_cost
    ON tb_import.productid = tb_cost.productid
JOIN tb_sites
    ON tb_sites.site_num = tb_import.site_cd
JOIN tb_product
    ON tb_product.prod_id = tb_import.productid
GROUP BY product_name, MONTH(date)
ORDER BY product_name, MONTH(date);

and then format the result in the presentation layer of the application.

Related Question