[FIXED] Mysql table Pivot to compare sales from current and previous year from the same table

Issue

I have created mysqltable with sales data from different years.link to sql fiddle

I want to have an column in the table where it shows sales from ThisYear and one extra column with total sum of sales from Lastyear, as shown in figure.

CREATE TABLE agreement (
    Id INT NOT NULL,
    supplier VARCHAR(25) NOT NULL,
      sales VARCHAR(25) NOT NULL,
      yearDate DATE NOT NULL,
  yearx VARCHAR(10)


);

INSERT INTO agreement
    (Id, supplier, sales, yeardate,yearx)
VALUES
    ('1', 'AB Foods', '2', '2020-01-01', '2020'),
    ('2', 'BC Foods', '2', '2020-01-01','2020'),
    ('11', 'AB Foods', '34', '2020-02-01', '2020'),
    ('22', 'BC Foods', '80', '2020-02-01','2020'),
    ('5', 'AB Foods', '5', '2021-01-03','2021'),
    ('6', 'BC Foods', '5', '2021-01-03','2021'),
     ('52', 'AB Foods', '51', '2021-02-03','2021'),
    ('16', 'BC Foods', '50', '2021-02-03','2021')
    
;

SQL Fiddle example

My approach to solve problem:

    SELECT
 supplier,
 yearx,
 sum(if(month(yearDate) = 1, sales, 0))  AS Jan,
 sum(if(month(yearDate) = 2, sales, 0))  AS Feb,
 sum(sales)  AS Total_ThisYear,
  sum(sales)  AS Total_LaastYear
FROM agreement
GROUP BY supplier, yearx
order by yearx

but seems I need a where clause inside the case statement.

Thank you in advance

Solution

Cannot do this without a join. This might be working solution:

SELECT
 a.supplier,
 a.yearx,
 
 sum(if(month(a.yearDate) = 1, a.sales, 0))  AS Jan,
 sum(if(month(a.yearDate) = 2, a.sales, 0))  AS Feb,
 sum(a.sales)  AS Total_ThisYear,
 IFNULL(ls.sales, 0)  AS Total_LaastYear

FROM agreement a

LEFT JOIN (
  SELECT b.supplier, YEAR(b.yearDate) AS 'year', SUM(sales) AS 'sales' FROM agreement b
  GROUP BY b.supplier, YEAR(b.yearDate)
) ls ON ls.supplier = a.supplier AND ls.year = yearx - 1

GROUP BY a.supplier, a.yearx

order by a.yearx

Answered By - Dzejkob