Grouping question

If I want to get a count of each type of item in a table, I can use a simply GROUP BY and include a Count() of what I want in the SELECT clause.

But how do I get subcounts of that data? Like I want to group by a first value, then show columns for all the possible values in a second column. For instance, let's say I have two columns FirstName and LastName. What would a t-sql statement look like to yield the following output? It's like using the Column gropuing in an Excel pivot table.

Is this possible?

	Totals
LastName	John	Jane
Doe	13	8
Schmoe	6	4

Thanks!

Dave

This is not possible to do in SQL without going through lot of hoops. You should do report generation on the client-side. Given few restrictions, you can generate a pivotted result like above.

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
>

GROUP BY rule holding me up - HELP!

I need to pull a new field (bpdeptracking.status) out of this existing query, but to include the field in the select I must also include it in the Group By. This changes the dynamic (and the output) of the query. So I need to figure out a way to keep the existing query intact and still obtain the bpdeptracking.status within the context of the parameters of this query. Can anyone help?

select min(calendarentries.entrydate) as firstdepo,prime.matterid
from dep join bpdeptracking on dep.depid=bpdeptracking.depid
join calendarentries on bpdeptracking.calendarid = calendarentries.calendarid
join depmaps on depmaps.depid = dep.depid
join ( select * from BP_ActivebyPara ) as prime on prime.matterid = depmaps.uniqueid
where calendarentries.entrydate <> '11/11/1911'
and (dep.deptype = 'P' or dep.deptype = 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group by prime.matteriduse min(bpdeptracking.status) in the SELECT list|||I'll give that a try. I appreciate the suggestion.|||Unfortunately, "use min(bpdeptracking.status) in the SELECT list" won't work because it returns the minimum value from bpdeptracking.status during the GROUP BY operation. As an example, the GROUP BY field is prime.matterid. If there are 10 rows that are identical based on this field, it will group them together and return the row that meets the min(calendarentries.entrydate) criteria. If I use min(bpdeptracking.status), it will return the minimum value for that column from the 10 rows in the group. I don't want the minimum value, I want the value in the row that is selected by the GROUP BY field using the min(calendarentries.entrydate) criteria.

So I will probably have to use 2 queries unless someone on this forum has an idea on how to accomplish this more efficiently.|||The use of GROUP BY produces an aggregate, it doesn't select any individual row. The two ideas are mutually exclusive, you can only have one or the other, never both in a single query.

By doing some creative "digital dancing", you can make it appear that you are getting a single row. If you can give us some sample data, and the results you'd like to see from that data, then I'd bet that we can help you to conjour it up!

-PatP|||if bpdeptracking.status is identical for every row in each prime.matterid group, then you should be able to take the min or the max, and it will be the same regardless

if it isn't the same for every row in each prime.matterid group, then either you (a) include it in the GROUP BY, which you say you can't do because it "changes the dynamic", or (b) pick any arbitrary value for bpdeptracking.status within each group (which is exactly what my choice of min does), or (c) realize that you cannot do what you are asking to do|||bpdeptracking.status is NOT identical for every row in each prime.matterid group, which is why I can't use MIN or MAX.

For example: say the DB has a group of 10 identical matterid values, each with a different entrydate. When I group those together by matterid and take the MIN(entrydate), I want the actual value of bpdeptracking.status that relates to the MIN(entrydate) for the prime.matterid group. If I use MIN(bpdeptracking.status), it returns the minimum alpha value for bpdeptracking.status from the 10 rows rather than the actual value in the selected row.

Is there a way to select bpdeptracking.status using the query below perhaps as a subquery or as 2 seperate queries to produce the desired result?|||aha! now we are getting someplace! :)

there may still be a problem, because if you want the row with the min entrydate, it may not be related to a status in ('Concluded','Continued')

select prime.matterid
, bpdeptracking.status
, c1.entrydate as firstdepo
from dep
join bpdeptracking
on dep.depid
= bpdeptracking.depid
join calendarentries as c1
on bpdeptracking.calendarid
= c1.calendarid
join depmaps
on dep.depid
= depmaps.depid
join BP_ActivebyPara as prime
on depmaps.uniqueid
= prime.matterid
where c1.entrydate <> '11/11/1911'
and c1.entrydate
= ( select min(entrydate)
from calendarentries
where calendarid
= bpdeptracking.calendarid
and entrydate <> '11/11/1911' )
and dep.deptype in ('P', 'P/IP')
and bpdeptracking.status in ('Concluded','Continued')
group
by prime.matterid
, bpdeptracking.status

Group by Problems

I know when you are using group by functions you have to include all
the columns in the GROUP BY clause.

But what I am having problems when using a case statement to determine
whether to sum of not a column.
eg.
SELECT Country,
Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
FROM WORLDTABLE
GROUP BY Country

The problem is that this statement will not work as it says that Age
should be in the group by clause. But if include it, then there is no
point in using the SQL statement.

So is there another way to do this than using SELECT statements for
columns as I am worried that it will be inefficient and hog system
resources.Don't know if this is the best way (depends on number of records in
table WorldTable and number of users on it) but here is one thing you
can do...
SELECT
WT.Country
, ISNULL(T1.YouthIncome, NULL) AS YouthIncome
, ISNULL(T2.PensionIncome, NULL) AS PensionIncome
FROM
dbo.WorldTable AS WT
LEFT OUTER JOIN (
SELECT
W1.Country
, SUM(W1.Income) AS YouthIncome
FROM
dbo.WorldTable AS W1
WHERE
W1.Age < 15
GROUP BY
W1.Country )
AS T1 ON WT.Country = T1.Country
LEFT OUTER JOIN (
SELECT
W2.Country
, SUM(W2.Income) AS PensionIncome
FROM
dbo.WorldTable AS W2
WHERE
W2.Age > 65
GROUP BY
W2.Country )
AS T2 ON WT.Country = T2.Country
ORDER BY
WT.Country|||ree32@.hotmail.com (ree32) wrote in message news:<7606ccc8.0502222025.651a643f@.posting.google.com>...
> I know when you are using group by functions you have to include all
> the columns in the GROUP BY clause.
> But what I am having problems when using a case statement to determine
> whether to sum of not a column.
> eg.
> SELECT Country,
> Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
> Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
> FROM WORLDTABLE
> GROUP BY Country
>
> The problem is that this statement will not work as it says that Age
> should be in the group by clause. But if include it, then there is no
> point in using the SQL statement.
> So is there another way to do this than using SELECT statements for
> columns as I am worried that it will be inefficient and hog system
> resources.

Try this

SELECT Country,sum(YouthIncome) as YouthIncome,sum(PensionIncome) as PensionIncome
FROM (
SELECT Country,
Case WHEN Age<15 THEN Income ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Income ELSE NULL END AS PensionIncome
FROM WORLDTABLE) as Deriv
GROUP BY Country|||SELECT country,
SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
FROM WORLDTABLE
GROUP BY Country

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1109168754.792871.91720@.l41g2000cwc.googlegroups.c om>...
> SELECT country,
> SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
> SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
> FROM WORLDTABLE
> GROUP BY Country

Thanks this is what I was looking for. Nice and easy.

Other posters thanks too.