0

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.

  • You are missing the aggregation in your calculated, per year fields. See the answers to the duplicate question. – Shadow Mar 26 '21 at 11:10
  • Thanks, ended up using max "max (case when year(grid_header.`EntryDate`) = 2018 then price else 0 end) as price_2018," looks good now. – themetaman Mar 26 '21 at 11:44

0 Answers0