Best Way to Count 3 sets - 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 2000 Forums

SQL Server Development (2000)

Best Way to Count 3 sets

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-19 : 10:31:24

I've got to total 3 counters. I've got 2 solutions to get the counts. Without comparing execution plans and empirical mesurement, any thoughts on which is the better approach?

Solution 1

SELECT	@BranchUSRTotal = COUNT(*)
	FROM	dbo.Users U
	WHERE	@BranchID IS NOT NULL
	AND	U.BranchID = @BranchID -- No need to test ClientID for specific BranchID
	AND	U.Inactive = 1 -- Active Users Only

SELECT	@CenterUSRTotal = COUNT(*)
	FROM	dbo.Users U
	WHERE	@CenterID IS NOT NULL
	AND	U.CenterID = @CenterID
	AND	U.Inactive = 1

SELECT	@ClientUSRTotal = COUNT(*)
	FROM	dbo.Users U
	WHERE	U.ClientID = @ClientID
	AND	U.Inactive = 1

Solution 2

SELECT	@ClientUSRTotal = COUNT(*) ,
	@CenterUSRTotal = SUM(CASE WHEN U.CenterID = @CenterID THEN 1 ELSE 0 END) ,
	@BranchUSRTotal = SUM(CASE WHEN U.BranchID = @BranchID THEN 1 ELSE 0 END)
	FROM	dbo.Users U
	WHERE	U.ClientID = @ClientID
	AND	U.Inactive = 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-19 : 11:02:02

Definitely the second. It's all in 1 result, it requires only 1 pass through the data, and it is shorter and easier to read/maintain.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources