ADD INSEET to SELECT QUERY for missing values

I have the following simplified table.

|---------------------|------------------|------------------|
|        month        |     customer     |      value       |
|---------------------|------------------|------------------|
|          1          |        A         |        20        |
|---------------------|------------------|------------------|
|          1          |        B         |        20        |
|---------------------|------------------|------------------|
|          1          |        C         |        20        |
|---------------------|------------------|------------------|
|          2          |        A         |        20        |
|---------------------|------------------|------------------|
|          2          |        B         |        20        |
|---------------------|------------------|------------------|

As you can see, There is not value for customer C, for month 2.

In this instance I need to programtically add month=’2′ customer=’C’ value=’0′, inside my select statement

SELECT month, customer, value FROM mytable

I have many more rows in my real table, but the end result needs to be the same customer list for each month.

Any help greatly appreciated.

Answer

Use a cross join to generate the rows and a left join to bring in the values:

select m.month, c.customer, coalesce(t.value, 0) as value
from (select distinct month from t) m cross join
     (select distinct customer from t) c left join
     t
     on t.month = m.month and t.customer = c.customer;

Note: If you have other sources for the months or customers, then those are probably faster than using count(distinct).

Advertisement

Answer