Re: simplify report generation by inserting bogus records with zero as value?

On 22 Apr 2002 18:12:07 -0700, ebradley4_at_yahoo.com (mbradley) wrote:

>In a table I call 'Generation,' there are columns for Plant ID, Fuel,
>Consumption (the number of fuel units consumed to generate the number
>of MWh in the 'Generation' field), and MWh(Megawatt Hours). The
>various columns in the Generation table establish relationships with
>other tables that allow me to (through joins) display generation and
>consumption summed by Plant, Plant Operator, State, SubRegion, and
>Region.
>
>The challenge is that, in many of the summations, it is desirable to
>display the totals by fuel and, for comparison purposes, return the
>same number of records for each entity by which the data is summed.
>So, for example, I want a query that returns summed data by state, but
>I want to make sure there is a state/fuel record for all fuels. If
>there is no use of a particular fuel in a particualr state (some
>states have no nuclear plants, most have no wind or solar powered
>plants), I still want a record for that fuel that corresponds to that
>state--but of course I want it have a zero value.

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 Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks