'SQL statement to get $0 if the customer has no billing from any specific year
I am trying to find the average billing amount per year from 2019 to 2021 for every customer, and I want to return $0 if the customer has no billing from any specific year. I tried doing a left join but unfortunately it does not give the result I need. In the table below, how do I return $0 for customer_id 1 since the year for 2021 does not exist. Thanks.
billing table:
| customer_id | billing_id | created_date | billing_amount |
|---|---|---|---|
| 1 | id_11 | 2019-06-21 | 100 |
| 1 | id_12 | 2020-05-11 | 126 |
| 1 | id_13 | 2019-12-28 | 86 |
| 2 | id_21 | 2019-12-28 | 28 |
| 2 | id_22 | 2020-12-28 | 56 |
| 2 | id_23 | 2021-12-28 | 26 |
Here is my incorrect query:
Select a.customer_id,
extract(year from a.created_date),
avg(a.billing_amount)
from billing as a
left join billing as b
on a.customer_id = b.customer_id
where a.created_date between '2019-01-01' and '2021-12-31'
group by 1, 2
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
