counting frequencies - 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)

counting frequencies

Author

Topic

waxdart23
Starting Member

33 Posts

Posted - 2004-09-24 : 09:33:07

I have a table which contains account numbers (account) and performances attended by the account holder (perf) within a season. So accounts can have multiple values of performances (because account holders can attend numerous performances) but each erformance can only have distinct accounts (because an account holder can only attend a performance once.

I am trying to write a statement that will count the number of accounts that attended the various frequencies of performances. I.e. xxx accounts attended one performance, yyy attended 2 performances and zzz attended 3 performances etc?

Can anyone help?

Thanks
P

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 09:37:57

it would help vastly if you gave us DDL and DML (create table stmts and insert stmts for sample data)

but here goes


Set ESP On

Select
	Performances,
	cnt = count(*)
From
	(
	Select <accountNum>, performances=count(*) 
	From <performanceHistory> 
	Group By <accountNum>
	) A
Group By Performances

Set ESP Off

Corey

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-24 : 09:38:37

Assuming all rows in the table has both an account and a per, then how about this:

SELECT account, COUNT(*)
FROM MyTable
GROUP BY account

Raymond

waxdart23
Starting Member

33 Posts

Posted - 2004-10-06 : 09:13:48

Seventhnight's was what I was after but raymondpeacock's was handy for something else I was doing with ther same data!

So thanks to both of you for your help.

Thanks
P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources