Group by a value and Average on other value, where other value became column

Question

I have this table.

country |   date     | cost
--------|------------|------
UK      | 2020-02-14 | 3.15
USA     | 2020-02-15 | 6.52
USA     | 2020-06-15 | 4.71
USA     | 2020-06-17 | 2.23
UK      | 2020-11-01 | 7.99
USA     | 2020-11-05 | 5.55
UK      | 2020-11-09 | 3.33

I want to make a query which gives me the result like this:

country | AVG-2020-02 | AVG-2020-06 | AVG-2020-11
--------|-------------|-------------|-------------
UK      | 3.15        | 0/null      | 5.66
USA     | 6.52        | 3.47        | 5.55

I want to group all rows by country and calculate the average cost based on the month. Where month became also a column.

Right now I have this:

SELECT tbname.country, AVG(tbname.cost)
FROM tbname
WHERE tbname.date LIKE "2020-%"
GROUP BY tbname.country

But this query gives me one column with average of rows grouped by country.

There can be made a query which can gives me the result like I want? And how?

Answer 1

You can use conditional aggregation:

select country,
    avg(case when month(date) = 1 then cost end) as avg_jan,
    avg(case when month(date) = 2 then cost end) as avg_feb,
    ...
from tbname
where date >= '2020-01-01'
group by country

It might be more efficient to do direct filtering in the avg():

select country,
    avg(case when date < '2020-02-01' then cost end) as avg_jan,
    avg(case when date >= '2020-02-01' and date < '2020-03-01' then cost end) as avg_feb,
    ...
from tbname
where date >= '2020-01-01'
group by country