Re: simplify report generation by inserting bogus records with zero as value?
Date: 23 Apr 2002 11:52:32 -0700
Message-ID: <ea8a03ff.0204231052.11493ca8_at_posting.google.com>
Nis Jorgensen <nis_at_dkik.dk> wrote in message >
> Sounds like an outer join, summation and then a conversion of null
> into zero. Something like
>
> SELECT State, Fuel, COALESCE(SUM(Consumption),0) AS TotalConsumption
> FROM (Fuels CROSS JOIN State)
> LEFT JOIN Generation
> ON Fuels.FuelID = Generation.FuelID
> AND State.StateID = Generation.STateID
> GROUP BY State, Fuel
Nis,
Yes, this is what I was looking for. In my (as you may have guessed) somewhat limited experience as a database designer, I haven't had many occassions to use outer joins. I know about them, but didn't think of using CASE or COALESCE to generate zeros in the fields that would otherwise be NULL. In fact, I wasn't familiar with the COALESCE keyword at all. Now that I've looked it up (thanks to your suggestion) I'm sure I'll find lots of uses for that one. This solves my problem. Thank you!
mbradley Received on Tue Apr 23 2002 - 20:52:32 CEST