Nested Query - Counts - 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

Transact-SQL (2000)

Nested Query - Counts

Author

Topic

scotchbroom
Starting Member

17 Posts

Posted - 2004-07-28 : 11:23:48

Hi,
I'm trying to get the following results from one table:

RepID, TerritoryString, AprilCount, JulyCount from Labels

The dates for the counts would be 'where dategenerated = '4/7/04' or 7/16/04' but I want to see the July & April results in their own columns so I can compare the quantities.

So far, I can only get one month to work correctly:

select RepID, TerritoryString, count (RepID) as July from labels
where dategenerated = '7/16/2004'
group by RepID, TerritoryString
order by RepID

n/a
deleted

35 Posts

Posted - 2004-07-28 : 11:43:44

Here is one way, there are many


select 	RepID, 
	TerritoryString, 
	sum(case when dategenerated = '07/16/2004' then 1 else 0 end) JulyCount,
	sum(case when dategenerated = '04/07/2004' then 1 else 0 end) AprilCount
from 	labels
where 	dategenerated in ('07/16/2004', '04/07/2004')
group by RepID, TerritoryString
order by RepID

HTH
Paul

scotchbroom
Starting Member

17 Posts

Posted - 2004-07-28 : 11:45:33

Paul -
Thanks!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources