Group Query to include 0 values -- sql server clients

PolarBears
3/9/2006 1:50:28 PM

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.

PolarBears
3/9/2006 4:47:27 PM

Thanks a ton!

[quoted text, click to view]

Hugo Kornelis
3/10/2006 12:09:47 AM

[quoted text, click to view]

(snip)
[quoted text, click to view]

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.)

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : Group Query to include 0 values