Need help on count query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on count query

Author

Topic

abhram
Starting Member

3 Posts

Posted - 2007-09-22 : 04:32:26

Hi,
I have a two tables as follows:
User Table:
UserID UserName
------------------
1 userA
2 userB
3 userC

Assignment Table:
AssignmentID Level TeamMember1 TeamMember2
--------------------------------------------------
1 A 1 2
2 B 3 1
3 A 2 3
4 C 1 3
...........

Now my query is to get the records in the following resultset:
UserNm AssigmnetCount LvlACount LvlBCount LvlCCount
------------------------------------------------------------
userA 3 1 1 1
userB 3 2 0 0
userC 3 1 1 1

What is the best possible way to achieve it? Cursor is ruled out since this is going to be used in a web application. can you guys guide me to best possible solution to it? Also one point to note is that the #of users is about 1500 and the number of assignments may be 1000+.

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 05:00:53

[code]
CREATE TABLE #User
(
UserID int NOT NULL PRIMARY KEY,
UserName varchar(20) NOT NULL
)

INSERT INTO #User
SELECT 1, 'userA' UNION ALL
SELECT 2, 'userB' UNION ALL
SELECT 3, 'userC'

CREATE TABLE #Assignment
(
AssignmentID int NOT NULL PRIMARY KEY,
Level CHAR(1) NOT NULL,
TeamMember1 int NOT NULL,
TeamMember2 int NOT NULL
)
INSERT INTO #Assignment
SELECT 1, 'A', 1, 2 UNION ALL
SELECT 2, 'B', 3, 1 UNION ALL
SELECT 3, 'A', 2, 3 UNION ALL
SELECT 4, 'C', 1, 3

SELECT
[UserNm] = UserName,
[AssigmnetCount] = COUNT(TeamMember),
[LvlACount] = SUM(CASE WHEN [Level] = 'A' THEN 1 ELSE 0 END),
[LvlBCount] = SUM(CASE WHEN [Level] = 'B' THEN 1 ELSE 0 END),
[LvlCCount] = SUM(CASE WHEN [Level] = 'C' THEN 1 ELSE 0 END)
FROM
(
SELECT UserName, [TeamMember]=TeamMember1, [Level]
FROM #User
LEFT OUTER JOIN #Assignment
ON TeamMember1 = UserID
UNION ALL
SELECT UserName, [TeamMember]=TeamMember2, [Level]
FROM #User
LEFT OUTER JOIN #Assignment
ON TeamMember2 = UserID
) AS X
GROUP BY UserName
ORDER BY UserName
GO
DROP TABLE #User
GO
DROP TABLE #Assignment
GO
[/code]
Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 05:02:06

"Also one point to note is that the #of users is about 1500 and the number of assignments may be 1000+."

Separate indexes on TeamMember1 and TeamMember2 would help.

Make sure you have PK or Index on UserID too.

Kristen

abhram
Starting Member

3 Posts

Posted - 2007-09-23 : 09:38:30

Thanks for the wonderful solution. I have one concern. Some coding standards says in SQL try to avoid UNION ALL. Is it going to be a potential performance spoiler? Whats your view on it?

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 02:09:11

"Some coding standards says in SQL try to avoid UNION ALL
Whats your view on it?"

Bollocks is that I would say!

It all depends ... but UNION ALL (rather than just UNION) would be fine for this job. Like any query it needs to be optimised if necessary (i.e. it takes a while to run, or the tables are huge, but often those aren't the case), hence you might need to add indexes as I mentioned above.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 02:11:26

quote:
Originally posted by abhram

Thanks for the wonderful solution. I have one concern. Some coding standards says in SQL try to avoid UNION ALL. Is it going to be a potential performance spoiler? Whats your view on it?

UNION is preferred if you want to have distinct rows otherwise UNION ALL is the way to go. Can you post the article where you read about UNION ALL?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources