I am very new to SQL and creating queries. I am trying to extract data to create some charts (in wpdatatables) that show year on year prices based on some criteria that I have set in the query. I am using case to build the year columns; I seem to be able to get data for one year but not the subsequent years.
Hoping someone can help, thanks in advance
My query is as follows.
select grid_calc.`Price`,
monthname(grid_header.`EntryDate`) as Month,
case when year(EntryDate) = 2018 then price else 0 end as price_2018,
case when year(EntryDate) = 2019 then price else 0 end as price_2019,
case when year(EntryDate) = 2020 then price else 0 end as price_2020,
case when year(EntryDate) = 2021 then price else 0 end as price_2021
FROM grid_calc
INNER JOIN grid_header
ON grid_header.`BuyerID` = '234'
AND grid_calc.`GridID` = grid_header.`GridID`
WHERE 1=1
AND grid_header.`EntryDate` > '2018-01-01'
AND grid_calc.`Teeth` = '2'
AND grid_calc.`FeedType` = '434'
AND grid_calc.`SexCategory` = '438'
group by monthname(EntryDate)
order by max(month(EntryDate));
The data I am getting..
Price Month price_2018 price_2019 price_2020 price_2021
4.8 January 4.8 0 0 0
4.85 February 4.85 0 0 0
5.05 March 5.05 0 0 0
4.9 April 4.9 0 0 0
4.7 May 4.7 0 0 0
4.65 June 4.65 0 0 0
4.85 July 4.85 0 0 0
4.85 August 4.85 0 0 0
4.8 September 4.8 0 0 0
5.2 October 5.2 0 0 0
6.85 November 0 0 6.85 0
P.S I know there is data in other years below is what I get when i only search on one year 2020
select grid_calc.`Price`,
monthname(grid_header.`EntryDate`) as Month,
case when year(EntryDate) = 2020 then price else 0 end as price_2020
FROM grid_calc
INNER JOIN grid_header
ON grid_header.`BuyerID` = '234'
AND grid_calc.`GridID` = grid_header.`GridID`
WHERE 1=1
AND grid_header.`EntryDate` > '2020-01-01'
AND grid_calc.`Teeth` = '2'
AND grid_calc.`FeedType` = '434'
AND grid_calc.`SexCategory` = '438'
group by monthname(EntryDate)
order by max(month(EntryDate));
Price Month price_2020
5.7 January 5.7
6 February 6
6.45 March 6.45
5.85 April 5.85
5.95 May 5.95
5.95 June 5.95
6.45 July 6.45
6.25 August 6.25
6.05 September 6.05
6.6 October 6.6
6.85 November 6.85
Cheers.