Aggregates
-
Can anyone tell me how to solve this problem I'm having. I'm sure it's possible I just dont know how.
I have a table with team results. These results reference a team table by team ID. I need to figure how to run a query that will return one row for each team and in each row give me the team name, a count of items in the table per team, the teams that have valid items in the table, invalid items and if there are no records for the team in the table. There are about 170 teams and I can get each one to work independently, but how do I get the counts for each all at once?
Thanks in advance for any help.
-
Dan,
Can you give some sample data to more clearify your problem.
Sample scripts of your table structure so that i can start working over it.
Amit Gupta..
/* Sloution is not always easy. */
-
Thanks for getting back to me so easy.
Here is the Teams table structure
-- TEAM TABLE
CREATE TABLE TEAMS(
UID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TEAM_NAME varchar(100) NOT NULL FOREIGN KEY REFERENCE(TEAM_NAMES),
PLAYER_NAME varchar(100),
PLAYER_YEAR char(4) NOT NULL,
PLAYER_ACTIVE char(1) NOT NULL
)
GO
-- ITEMS TABLE
CREATE TABLE ITEMS(
UID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TEAM_ID int FOREIGN KEY REFERENCES TEAMS(UID)
TEAM_MEMBER varchar(100),
MOVEMENT varchar(100)
)
GO
Now what I'm trying to do is see how many teams have records in the ITEMS table, how many TEAM_MEMBER's are in the ITEMS table, how many TEAM_MEMBERS are not in the ITEMS table and how many are
ACTIVE and INACTIVE.
I hope this helps and if not I can provide more detail.
Thanks again for your help!
Dan
-
Dan
This DDL doesn't make sense, for the following reasons:
(1) The syntax for the foreign key constraint in the first table is wrong
(2) You're missing a comma from the second table definition.
Please right-click on the table names in Enterprise Manager, choose Generate Script and post the results so that we can see what the table structure is.
Thanks
John
-
Sorry about that, I was just trying to save space since the tables have an extreme amount of columns, guess I should check my re-typing of the data before I ask for help. I inherited this so please bear with me as I know it's not designed the way I would prefer. This should be correct, though I did leave out the other columns still or else you'd have to scroll for ever just to read it.
-- TEAM PROFILE
CREATE TABLE TEAM_PROFILE(
UID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TEAM_NAME varchar(100) NOT NULL,
TYPE int FOREIGN KEY REFERENCES TEAM_TYPES (UID),
DATE_ADDED smalldatetime NOT NULL,
ACTIVE char(1) NOT NULL
)
GO
-- TEAM TABLE
CREATE TABLE TEAMS(
UID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TEAM_NAME varchar(100) NOT NULL FOREIGN KEY REFERENCES TEAM_PROFILE(TEAM_NAME),
PLAYER_NAME varchar(100),
PLAYER_YEAR char(4) NOT NULL,
PLAYER_ACTIVE char(1) NOT NULL
)
GO
-- ITEMS TABLE
CREATE TABLE ITEMS(
UID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
TEAM_ID int FOREIGN KEY REFERENCES TEAMS(UID),
TEAM_MEMBER varchar(100),
MOVEMENT varchar(100)
)
GO
Hopefully I didnt mess this on up too and it helps. If you'd prefer I can get you the complete listing but it's going to be a while since I'm not able to access the db at the moment because of a backup issue though I can retype it but it will take a while. Please let me know if this isn't sufficient.
Thanks again,
Dan
-
Dan
I'm afraid this still doesn't make sense. You can't have a foreign key constraint in TEAMS referencing the TEAM_NAME column in TEAM_PROFILE since that column does not have a unique or primary key constraint on it. I suspect you should be referencing the UID column. Did you generate this script from Enterprise Manager? I don't see how you could have created tables with these definitions in the first place.
Second, please be more clear what you mean in your first post by valid and invalid items. You say that you have a table with team results, but I can't see a column that looks as if it holds results. What does the ITEMS table do?
Third, please provide some sample data for each table in the form of INSERT statements so that I can test any query I come up with.
Thanks
John
-
I create a lot of SQL statements with aggregrates and trying to get multiple aggregrate values with different underlying conditions into a single statement is quite difficult and very frail. (maybe you ought to post the individual queries). Without seeing them, I couldn't determine if what you want done is even possible. But off the cuff, you have a high level of complexity, especially with the outer join conditions.
The good news is that you are successful in each individual query. My suggestion is to work with that. Here's my strategy. Build a table structure that will contain the fields you want in your final query (Team_Name, 1st_stat, 2nd_stat, etc.). Structure it as if it were to be the final report. Then convert each individual query into an update query, updating the table with the appropriate value on each team. Gang all the updates into a script - and you have your solution. The bonus is that this script will be 10 TIMES easier to debug and maintain since you are only working with one value at a time.
Mike
-
Dan,
1. Do you want all these aggregates in one resultset? What format?
2. Is the PLAYER_ACTIVE column a char(1)? Why not a bit? What are the possible values in it? ('Y' or 'N' only?)
3. Please specify exactly what you want.
-
SELECT
TEAMNAME,
(SELECT COUNT(*) FROM TEAMRESULTS WHERE TEAM_ID = TEAMS.TEAM_ID) AS ITEMS_TOTAL,
(SELECT COUNT(*) FROM TEAMRESULTS WHERE TEAM_ID = TEAMS.TEAM_ID AND ITEM=VALID) AS ITEMS_VALID,
(SELECT COUNT(*) FROM TEAMRESULTS WHERE TEAM_ID = TEAMS.TEAM_ID AND ITEM=INVALID) AS ITEMS_INVALID
FROM
TEAMS
The first column also covers the matter concerning teams without items.
HTH
Jurriaan
-
Selects nested inside of selects can be replaced with a nested aggregate and then a left outer join. As the table being aggregated is being read once instead of three times, there is a signficant difference in performance.
SELECT Teams.Team_Id
,Teams.TeamName
,TeamResultCnt
,TeamResultCnt_Valid
,TeamResultCnt - TeamResultCnt_Valid
as TeamResultCnt_InValid
FromTeams
LEFT OUTER JOIN
( SELECTTeamResults.TeamId
,COUNT(*)as TeamResultCnt
,SUM ( CASE WHEN ?item is valid condition? then 1
else 0 end) as TeamResultCnt_Valid
fromTeamResults
group by TEAMRESULTS.TeamId
) As TeamSummary
on TeamSummary.TeamId = TeamSummary.TeamId
SQL = Scarcely Qualifies as a Language
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply