MySQL help making query

Question

I need to display the consumption of products for 6 months.

My table Consumption is made as:

int ID_Conso
int ID_Product
int Quantity_Conso
int Month_Conso
int Year_COnso

there is 1 record/1 product/1 month as shown in picture below

the query I want to write for my view must show the result as :

Here is my query :

      SELECT c.id_Product,
         t1.conso1, 
         t2.conso2, 
         t3.conso3, 
         t4.conso4, 
         t5.conso5, 
         t6.conso6
  FROM Consumption c,
     (SELECT quantity_conso as "conso1" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 1) t1,
     (SELECT quantity_conso as "conso2" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 2) t2,
     (SELECT quantity_conso as "conso3" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 3) t3,
     (SELECT quantity_conso as "conso4" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 4) t4,
     (SELECT quantity_conso as "conso5" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 5) t5,
     (SELECT quantity_conso as "conso6" 
            FROM `consumption` 
            WHERE year= Year(Now()) and month = 6) t6

this query displays all the records for all the id_product (doesn't display null if one record is missing)

I have tried to use if exist but without success.

Answer 1

Use aggregation function SUM with IF, and GROUP BY on Id_Product. Try the following query:

SELECT 
  Id_Product, 
  SUM(IF(Month_Conso = 1, Quantity_Conso, 0)) AS QtyConso_Month1, 
  SUM(IF(Month_Conso = 2, Quantity_Conso, 0)) AS QtyConso_Month2, 
  SUM(IF(Month_Conso = 3, Quantity_Conso, 0)) AS QtyConso_Month3, 
  SUM(IF(Month_Conso = 4, Quantity_Conso, 0)) AS QtyConso_Month4, 
  SUM(IF(Month_Conso = 5, Quantity_Conso, 0)) AS QtyConso_Month5, 
  SUM(IF(Month_Conso = 6, Quantity_Conso, 0)) AS QtyConso_Month6 
FROM Consumption 
GROUP BY Id_Product 
ORDER BY Id_Product ASC