merging two queries into one - 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

General SQL Server Forums

New to SQL Server Programming

merging two queries into one

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2010-09-30 : 10:32:17

Hi, many thanks in advance

I have two queries:

-- 1) report usage by territory
select max(TerritoryName) as territory, SUM(reportcount) as reportcount from View_BIUsers_ReportData where ReportID = 'aotm' and TreeYear = '2010' group by TerritoryName order by SUM(reportcount) desc

This gives me a reportcount by territory and returns 2 columns territory, reportcount

--2) number of users by territory
select COUNT (distinct UserName) as count, TerritoryName from View_BIUsers_ReportData group by territoryname order by count desc

This gives me a usercount by territory and returns 2 columns:
territory, number of users

What I need?
I need to merge the two queries into one which returns 4 columns:
territory, reportcount, no of users, reportcount/no of users

The below doesnt quite work:
select territoryname,COUNT (distinct UserName) as count,

(select SUM(reportcount) as territory from View_BIUsers_ReportData where ReportID = 'aotm' and TreeYear = '2010') as RC
from View_BIUsers_ReportData
group by territoryname
order by count desc

Thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-30 : 11:01:26

Something like this?

SELECT rd.territoryName as territory
, SUM(CASE WHEN rd.ReportID = 'aotm' and rd.TreeYear = '2010' THEN rd..reportcount ELSE 0 END) as reportcount
, COUNT (distinct rd.UserName) as Users
, SUM(CASE WHEN rd.ReportID = 'aotm' and rd.TreeYear = '2010' THEN rd..reportcount ELSE 0 END)*1.0
/ COUNT (distinct rd.UserName)
FROM View_BIUsers_ReportData rd

GROUP BY rd.territoryName

Jim

Everyday I learn something that somebody else already knew

tariq2
Posting Yak Master

125 Posts

Posted - 2010-09-30 : 11:19:51

Jimf, thats great, thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-30 : 11:21:47

You're welcome

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources