--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> If there are any miners who had no shift records for 2004/2 the

left join

> returns a row to the intermediate (pre-grouping) set, containing

m.code

> with nulls for t.tons, t.yr and t.mo. So far, so good.
>
> But the sum() aggregation ignores rows with null in t.tons, so

there is

> nothing to output from the aggregation for those miners.
>
> You'll need to find a way to get a value (0) into the picture

before the

> aggregation occurs. Exactly how you do that depends on the

database

> dialect and the server version you are using.

Actually, I was able to rearrange the joins to specify one that
will return a record for each miner at first, then left join the
others to that, and it finally produces the NULLs where it should.
That plus creating some missing indexes (D'OH!) made it work
correctly AND about 100x (no exaggeration) faster.

Thanks Helen, your comments got me on the right track!

The final query is more complex than the example I gave initially,
and looks like this (if anyone is interested):

select m.code miner,
l.tons l_tons,
sum(sy.tons) sy_tons,
sm.tons sm_tons,
sum(ry.tons) ry_tons,
rm.tons rm_tons
from miners m
join life_srv_tons_m l on l.miner_id = m.id
join srv_tons_month_m sm on sm.miner_id = m.id
join srv_tons_month_m sy on sy.miner_id = m.id
and sy.yr = sm.yr
and sy.mo <= sm.mo
left join rep_tons_month_m rm on rm.miner_id = sm.miner_id
and rm.yr = sm.yr
and rm.mo = sm.mo
left join rep_tons_month_m ry on ry.miner_id = sm.miner_id
and ry.yr = sm.yr
and rm.mo <= sm.mo
where sm.yr = :REP_YEAR and sm.mo = :REP_MONTH
group by m.code, l.tons, sm.tons, rm.tons

-Terry