Need help with a SELECT statement!

Question

Post reply

Need help with a SELECT statement!

drickman

Newbie

Points: 2
More actions
April 24, 2003 at 9:45 am

#159491

Is there an easy way to return what the "average" or majority of a particular varchar field is? For example, say I have 6 records, if the field STATUS has the following per record "D, A, A, A, A, D" I want it to return "A" because it occurs most often. Can this be done easily?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

what about

SELECT TOP 1 <field_name>, COUNT(<some_field> AS tmp FROM <table_name>

GROUP BY <field_name> ORDER BY tmp DESC.

That'll only return the value which occurs most often. Leave out the TOP 1, you'll get a ranking.

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Just correcting a typo in his SQL statement:

SELECT TOP 1 <field_name>, COUNT(<some_field>) AS tmp

FROM <table_name>

GROUP BY <field_name>

ORDER BY tmp DESC

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

oops, sorry if it has led to confusion

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

drickman Newbie Points: 2 More actions · Answer 4

Thanks!! worked like a charm

Cheers to you too!