Skip to content
Advertisement

ADD INSEET to SELECT QUERY for missing values

I have the following simplified table.

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.

Advertisement

Answer

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

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement