CODEWITHSUNDEEP
mysqlsql
Emmanuel
Emmanuel

Reputation: 58

How to generate price comparison report in the mysql

I have a dated brent prices table that keeps a price list from 1 January to 31 January. I want to compare the price for the 1th of January 2020 (or other date) with the first of January 2021 (or other date), The report will have date, year like 2020 and 2021 with the prices for 2020 displaying under 2020 and the prices for 2021 displaying under 2021.I have written the below query but it does not.

SELECT b.date
     , b1.price as year1
     , b2.price as year2 
  from brentprices as b 
  join brentprices as b1 
    on b1.year = '2020'
  join brentprices as b2 
    on b2.year = '2021'

Below is the sample table and data

enter image description here

Below is the expected result

enter image description here

Upvotes: 0

Views: 81

Answers (1)

Luuk
Luuk

Reputation: 14978

tabular text and images are different... (but that's another discussion):

create table brentprices (date date primary key, price decimal(8.2));

insert into brentprices values ('2020-01-01', 100);
insert into brentprices values ('2020-01-02', 101);
insert into brentprices values ('2020-01-03', 102);
insert into brentprices values ('2021-01-01',  99);
insert into brentprices values ('2021-01-02', 103);
insert into brentprices values ('2021-01-03', 102);

select * 
from brentprices b1 
left join brentprices b2 on b2.date=date_add(b1.date,INTERVAL 1 YEAR) 
where year(b1.date)=2020;

output:

+------------+-------+------------+-------+
| date       | price | date       | price |
+------------+-------+------------+-------+
| 2020-01-01 |   100 | 2021-01-01 |    99 |
| 2020-01-02 |   101 | 2021-01-02 |   103 |
| 2020-01-03 |   102 | 2021-01-03 |   102 |
+------------+-------+------------+-------+

Upvotes: 1

Related Questions