Group Query to include 0 values

I have to sum(NoWidgets) produced GROUP'ed By Station. The Widgets Produced
are in a subtable of the Widget Stations, so it works for the most part by
doing this:
Select Sum(NoWidgets) From WidgetData Inner Join StationData GROUP BY Station
Where I run into problems is when I want to sum the number of blue widgets
by Station. Because I want to know even if a station produced 0 blue widgets.
If I say:
Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
'Blue' GROUP BY Station
Then the stations that had no blue widgets are not included in the output.
I want ALL stations in the output, even if they have a value of 0. I'm also
wanting to do an average, and I want that based upon all stations, whether or
not any blue widgets were produced.
Seems like it should be easy to do, and I'm sure I'm showing myself to be a
novice for not know how that is done.
Thanks!
On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
(snip)
>Where I run into problems is when I want to sum the number of blue widgets
>by Station. Because I want to know even if a station produced 0 blue widgets.
>If I say:
>Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
>'Blue' GROUP BY Station
>Then the stations that had no blue widgets are not included in the output.
>I want ALL stations in the output, even if they have a value of 0. I'm also
>wanting to do an average, and I want that based upon all stations, whether or
>not any blue widgets were produced.
Hi PolarBears,
Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
BY clause:
SELECT SUM(NoWidgets)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
WHERE Color = 'Blue'
GROUP BY ALL Station
Note that this works in SQL Server 2000 and SQL Server 2005, but the
GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
it will be removed in a future version).
If you prefer a portable, ANSI-standard version, you can use
SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
GROUP BY Station
(Note: both queries above are untested. See www.aspfaq.com/5006 ff you
prefer a tested query.)
Hugo Kornelis, SQL Server MVP
|||Thanks a ton!
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
> (snip)
> Hi PolarBears,
> Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
> BY clause:
> SELECT SUM(NoWidgets)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> WHERE Color = 'Blue'
> GROUP BY ALL Station
> Note that this works in SQL Server 2000 and SQL Server 2005, but the
> GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
> it will be removed in a future version).
> If you prefer a portable, ANSI-standard version, you can use
> SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> GROUP BY Station
> (Note: both queries above are untested. See www.aspfaq.com/5006 ff you
> prefer a tested query.)
> --
> Hugo Kornelis, SQL Server MVP
>