Customer order total by month, w/ all customers listed, even if customer had no orders in a month, in one SQL statement?

Question

Get a list of all customers order totals by month, and if the customer has no order in a given month, include a line for that month with 0 as the order total. In one statement? Totals already computed, no need for aggregate function.

Use of the coalesce function is acceptable.

Given list of customer order totals by month:

create table orders (cust char(1), month num, exps num);
insert into orders
    values('a', 1, 5)
    values('b', 2, 4)
    values('c', 1, 8);

And a list of customers:

create table custs(cust char(1));
insert into custs
    values('a')
    values('b')
    values('c')
    values('d');

Generate this table:

cust, month, exps
 a, 1, 5
 a, 2, 0
 b, 1, 0
 b, 2, 4
 c, 1, 8
 c, 2, 0
 d, 1, 0
 d, 2, 0

Answer 1

select or1.cust, a.[month], sum(coalesce(or2.[exps], 0)) as exps
from (
    select 1 as[month] union all select 2
) a cross join (select distinct cust from custs) or1
left join orders or2 on or2.[month] = a.[month] and or2.cust = or1.cust
group by or1.cust, a.[month]
order by or1.cust,a.[month]

Sqlfiddle

And another version with picking up all existing months from the table. Results are same for our test data:

select or1.cust, a.[month], sum(coalesce(or2.[exps], 0)) as exps
from (
    select distinct [month] from orders
) a cross join (select distinct cust from custs) or1
left join orders or2 on or2.[month] = a.[month] and or2.cust = or1.cust
group by or1.cust, a.[month]
order by or1.cust,a.[month]

Sqlfiddle