Record Counts Updates - 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

SQL Server Development (2000)

Record Counts Updates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-25 : 09:21:16

Nathan writes "In one single table we have individual schools and school district, schools and districts are like child-parent, I need a stored procedure to count how many schools we have under each district. Then we will take the result (school count) to assign sales reps. If a district has 0-5 schools, we assign to an inside sales rep, district with 6+ schools, we assign it to an employee reps ."

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-25 : 09:49:37

A simple GROUP BY query will allow you to count the number of schools per district:-

SELECT district,count(*)
FROM schools
GROUP BY district

You could flag each district using a CASE on the COUNT:-

SELECT 
	district,
	district_type = 
		CASE 
			when count(*) between 0 and 5 then '0-5' 
			when count(*) > 6 then '6+'
			else 'XXX'
		end
FROM schools
GROUP BY district

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources