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 allCountFROM @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.resultCountFROM (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??