How to combine two grouped result sets

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)

How to combine two grouped result sets

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-02-15 : 09:15:23

I have a procedure that performs various operations on two table variables. At the very end of the procedure, I have this code to display totals:


SELECT 
	r.regionID,
	r.regionName, 
	COUNT(DISTINCT(r.userID)) as resultCount	
FROM 
	@tbl_results r 
GROUP BY 
	r.regionID,
	r.regionName

		
SELECT 
	a.regionID,
	r.regionName,
	COUNT(a.userID) as allCount
FROM 
	@tbl_UsersAll a LEFT OUTER JOIN
	[tbl_Regions] r ON a.regionID = r.ID 
GROUP BY 
	a.regionID,
	r.regionName

The code above produces the following results in SSMS:

Is it possible however to move the resultCount column from the first select to site alongside the results of the second select statement (cue Photoshop...)?

I have tried it myself and achieved what I need, but I think my solution is messy. Is there a better way than this?


SELECT
	ra.regionID,
	ra.regionName,
	ra.allCount,
	rc.resultCount
FROM
	(SELECT 
		a.regionID,
		r.regionName,
		COUNT(a.userID) as allCount
	FROM 
		@tbl_UsersAll a LEFT OUTER JOIN
		[tbl_Regions] r ON a.regionID = r.ID 
	GROUP BY 
		a.regionID,
		r.regionName) ra INNER JOIN
	(SELECT 
		r.regionID,
		r.regionName, 
		COUNT(DISTINCT(r.userID)) as resultCount	
	FROM 
		@tbl_results r 
	GROUP BY 
		r.regionID,
		r.regionName) rc ON COALESCE(ra.regionID,-1) = COALESCE(rc.regionID, -1) -- messy??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:45:59

you've written it in right way. unless you convert NULL values to something else like what you've done you wont be able to link values to same row like in your resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-02-15 : 10:34:40

Hi visakh16

That's great - thank you very much for confirming!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 14:59:35

no problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources