Nested Group By? -- sql server programming

sql server programming : Nested Group By?

Solel Software
6/27/2005 7:01:01 PM

Hi,

I have a couple ways that I figured out how to do a query but am not sure of
the best way. I was wondering if any of you knew which is "better" for T-SQL.
Here's the SQL for setting up the tables:

CREATE TABLE [Teams] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [TeamMembers] (
[TeamID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
CONSTRAINT [IX_TeamMembers] UNIQUE NONCLUSTERED
(
[TeamID],
[PersonID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Persons] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [PersonServices] (
[ServiceID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[Status] [tinyint] NOT NULL,
[Scheduled] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [Services] (
[ServiceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
[ServiceID]
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [Teams]([Name])
VALUES('Bob''s Team') -- TeamID 1

INSERT INTO [Teams]([Name])
VALUES('Betty''s Team') -- TeamID 2

INSERT INTO [Persons]([Name])
VALUES('Bob') -- PersonID 1

INSERT INTO [Persons]([Name])
VALUES('Betty') -- PersonID 2

INSERT INTO [Persons]([Name])
VALUES('Greenwald') -- PersonID 3

INSERT INTO [TeamMembers]([TeamID],[PersonID])
VALUES(1,1) -- Add Bob to Bob's Team

INSERT INTO [TeamMembers]([TeamID],[PersonID])
VALUES(2,2) -- Add Betty to Betty's Team

INSERT INTO [TeamMembers]([TeamID],[PersonID])
VALUES(1,3) -- Add Greenwald to Bob's Team

INSERT INTO [Services]([Name])
VALUES('Thursday Service') -- ServiceID 1

INSERT INTO [Services]([Name])
VALUES('Friday Service') -- ServiceID 2

INSERT INTO [PersonServices]([ServiceID],[PersonID], [Status], [Scheduled])
VALUES(1,1,1, '6/23/2005') -- Add Thursday Service for Bob, Status 1

INSERT INTO [PersonServices]([ServiceID],[PersonID], [Status], [Scheduled])
VALUES(1,1,2, '6/16/2005') -- Add Thursday Service for Bob, Status 2

INSERT INTO [PersonServices]([ServiceID],[PersonID], [Status], [Scheduled])
VALUES(2,2,1, '6/24/2005') -- Add Friday Service for Betty, Status 1

INSERT INTO [PersonServices]([ServiceID],[PersonID], [Status], [Scheduled])
VALUES(3,1,3, '6/23/2005') -- Add Thursday Service for Bob, Status 3

DROP TABLE Teams
DROP TABLE TeamMembers
DROP TABLE Persons
DROP TABLE PersonServices
DROP TABLE Services

I'm looking to get a query that for each team, gives the team name, number
of members on the team, number of services with Status 1, number of services
with Status 2, number of services with Status 3, the date + service +
PersonID of the most recent service for the team. It seems that I could do
this via aggregates (Count(*) and Max()) or sub-queries. I'm not sure how,
exactly, to do this via aggregates but I got this far:

Select T.TeamID, Count(*) AS NumMembers From Teams T LEFT OUTER JOIN
TeamMembers TM ON T.TeamID = TM.TeamID GROUP BY T.TeamID

I'm just not sure how to add the other aggregate functions on other joined
tables to this. Is it even possible to do that? If so, how do you tell SQL
which table the Count(*) should be on? Or are aggregate functions (via Group
By) limited to only two joined tables? I know how to do this via sub-queries
for each value

Select T.TeamID, (Select Count(*) From TeamMembers Where TeamID = T.TeamID)
AS NumMembers, ... From Teams T

but if I could do this via group by's it would be cool to learn how. Thanks!

--
Sincerely,

oj
6/27/2005 9:56:33 PM

You can try this:

Select T.TeamID, Count(*) AS NumMembers,SUM(Stat1) SM_Stat1, SUM(Stat2)
SM_Stat2,SUM(Stat3) SM_Stat3,Max(dt) Mx_Date,MAX(X.PersonID) PersonID
From Teams T LEFT OUTER JOIN TeamMembers TM ON T.TeamID = TM.TeamID
LEFT JOIN (select PS.PersonID,SUM(case when PS.Status=1 then 1 else 0 end)
Stat1,
SUM(case when PS.Status=2 then 1 else 0 end) Stat2,
SUM(case when PS.Status=3 then 1 else 0 end) Stat3,
Max(Scheduled) dt
from PersonServices PS
group by PS.PersonID) X ON TM.PersonID=X.PersonID
GROUP BY T.TeamID

--
-oj

[quoted text, click to view]

Paul Nielsen
6/27/2005 10:49:19 PM

Thanks for the fun, here's the first half of the query, of course there are
other solutions.

Please check the results with what you expect

/*
-Paul Nielsen
www.SQLServerBible.com
www.SolidQualityLearning.com
*/

SELECT [Name],
(SELECT Count(*) FROM TeamMembers WHERE TeamID = T.TeamID) as Members,
(SELECT Count(*)
FROM Services WHERE ServiceID IN
(Select ServiceID
FROM PersonServices PS
JOIN TeamMembers TM ON PS.PersonID = TM.PersonID
WHERE PS.Status = 1
AND TM.TeamID = T.TeamID)) as Status1,
(SELECT Count(*)
FROM Services WHERE ServiceID IN
(Select ServiceID
FROM PersonServices PS
JOIN TeamMembers TM ON PS.PersonID = TM.PersonID
WHERE PS.Status = 2
AND TM.TeamID = T.TeamID)) as Status2,
(SELECT Count(*)
FROM Services WHERE ServiceID IN
(Select ServiceID
FROM PersonServices PS
JOIN TeamMembers TM ON PS.PersonID = TM.PersonID
WHERE PS.Status = 2
AND TM.TeamID = T.TeamID)) as Status3
FROM Teams T

Results:

Name Members Status1 Status2
Status3
-------------------------------------------------- ----------- ----------- -----------
-----------
Bob's Team 2 1 1
1
Betty's Team 1 1 0
0

[quoted text, click to view]

Paul Nielsen
6/27/2005 10:54:36 PM

Hi Mark,

Question - if there are two services on the same day, how do you want to
handle the tie?

-Paul

[quoted text, click to view]

Solel Software
6/28/2005 11:57:03 AM

Paul and oj,

Thank you so much for your help! Paul, thanks for your sub-query based
answer. I had put one together already and was attempting to figure out it
there was some way to avoid the redudant multiple joins in each of the
sub-queries (using group by's), which is _exactly_ what oj responded with.

oj, Thank you _so_ much for your help. I didn't even know that was
possible to do a left join on a dynamic temp table in T-SQL, and now I know
how! This is perfect and will be extremely helpful. Thank you again!

--
Sincerely,

Mark Fox

[quoted text, click to view]

oj
6/28/2005 1:04:28 PM

You're very welcome.

Thanks for posting ddl+sample data. It was much easier to help. ;-)

--
-oj

[quoted text, click to view]

--CELKO--
6/28/2005 1:29:05 PM

First, we need to clean up the DDL. Your tables lacked DRI actions,
proper keys, constraints and the vaguely named "name" column appeared
everywhere. I think this is what you meant

CREATE TABLE Teams
(team_id INTEGER NOT NULL PRIMARY KEY,
team_name VARCHAR(25) NOT NULL);

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY,
person_name VARCHAR(50) NOT NULL);

CREATE TABLE Services
(service_id INTEGER NOT NULL PRIMARY KEY
service_name VARCHAR(50) NOT NULL);

CREATE TABLE TeamMembers
(team_id INTEGER NOT NULL
REFERENCES Teams(team_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
person_id INTEGER NOT NULL,
REFERENCES Persons(person_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (team_id, person_id));

CREATE TABLE PersonServices
(service_id INTEGER NOT NULL
REFERENCES Services(service_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
person_id INTEGER NOT NULL,
REFERENCES Persons(person_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
schedule_date DATETIME NOT NULL,
service_status INTEGER NOT NULL
CHECK (service_status IN (1,2,3))
PRIMARY KEY (service_id, person_id,schedule_date));

[quoted text, click to view]

That is not a query; it is a report because you have mixed levels of
aggregation. You do not concatenate columns into a jumble on the
database side of the system. Let's start with the first query in your
request.

SELECT T1.team_id, T1.team_name,
SUM (CASE WHEN S1.service_status = 1 THEN 1 ELSE 0 END) AS
stat_1_tally,
SUM (CASE WHEN S1.service_status = 2 THEN 1 ELSE 0 END) AS
stat_2_tally,
SUM (CASE WHEN S1.service_status = 3 THEN 1 ELSE 0 END) AS
stat_3_tally
FROM Teams AS T1, PersonServices AS S1, TeamMembers AS M1
WHERE S1.person_id = M1.person_id
AND M1.team_id = T1.team_id
GROUP BY T1.team_id, T1.team_name;

Now the second for last serviced players:

SELECT T1.team_id, T1.team_name, S1.person_id, S1.service_id,
S1.service_date
FROM Teams AS T1, TeamMembers AS M1, PersonServices AS S1
WHERE S1.person_id = M1.person_id
AND M1.team_id = T1.team_id
AND S1.service_date
= (SELECT MAX S2.service_date
FROM PersonServices AS S2, TeamMembers AS M2
WHERE S2.person_id = M2.person_id
AND M2.team_id = T1.team_id);

(Untested)

Solel Software
6/28/2005 5:16:02 PM

Dear Joe,

As always, thank you for your helpful response! I didn't realize that the
DDL had to be in a special format. I will clean the T-SQL up in posts from
now. Thank you for the example!

You are correct that this is a "report" and not a "query" The mixed levels
of aggregation are what I wasn't sure how to handle with Group By's. Thank
you for your query. Between that and oj's I should be set!

--
Sincerely,

Mark Fox

[quoted text, click to view]

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 programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn