Aggregate Count - 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)

Aggregate Count

Author

Topic

ausmoran
Starting Member

5 Posts

Posted - 2005-08-04 : 14:14:32

I would like to be able to group in a set way but am not sure how to do it. Instead of simply counting members in each office, I would like to be able to show an aggregate group of office in the following way:

1 person offices: nnnn count of members % of total members
2 thru 10 member offices: nnnn count of members % of total members
11 thru 25 member offices: nnnn count of members % of total members
26 thru 40 member offices: nnnn count of members % of total members
41 and higher: nnnn count of members % of total members
TOTAL COUNT:

Here is the preliminary SELECT statement that I've started to work with. I must confess to being very new to T-SQL so I only understand the VERY basics. I very much appreciate any assistance you might be able to offer me. Thanks in advance.


SELECT Member.Office_Number, Count(Member.NRDS_ID) AS CountOfNRDS_ID
FROM Member, member_Association 
WHERE Member.Member_Number=Member_Association.member_Number 
	AND Member_Association.status='a'
	AND Member_Association.Primary_Indicator='p'
	AND (Member_Association.Member_Type_Code='r' OR Member_Association.Member_Type_Code='RA')
GROUP BY (Member.Office_Number
Order BY Member.Office_Number

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-04 : 22:47:42

At first glance, I'd throw the results from your select into a temporary table


SELECT Member.Office_Number, Count(Member.NRDS_ID) AS CountOfNRDS_ID
INTO #Temp
FROM Member, member_Association 
WHERE Member.Member_Number=Member_Association.member_Number 
	AND Member_Association.status='a'
	AND Member_Association.Primary_Indicator='p'
	AND (Member_Association.Member_Type_Code='r' OR Member_Association.Member_Type_Code='RA')
GROUP BY (Member.Office_Number

Then UNION the results together to get what you want

SELECT '1 person offices:', COUNT(*) As Total
  FROM #Temp
  WHERE CountOfNRDS_ID = 1 -- Offices with one, the loneliest number (TDN)
  UNION ALL
  SELECT '2 thru 10 member offices:', COUNT(*) As Total
  FROM #Temp
  WHERE CountOfNRDS_ID BETWEEN 2 AND 10 -- 2 can be as bad as 1
  UNION ALL
  et cetera

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources