By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Ranking records with how many times they exist

100+
P: 539
For example:

Cat
Dog
Cow
Rat
Cat
Cat
Rat
Cow
Cow
Cow

(RESULT)
Where it exist:
Cow 4 times
Cat 3 times
Rat 2 times
Dog 1 time

I would like to have a result like the one i mentioned above (RESULT)

How would it be queried like?

The current implementation has 2 queries, 1st is to select them distinctly, 2nd is one by one, each of the unique result will select again the db to count how many times it exist, i think it is a bad implementation and i hope it is possible to query once in the DB Server, any ideas?
Jan 6 '10 #1
Share this Question
Share on Google+
3 Replies


mwasif
Expert 100+
P: 801
This can be achieved by COUNT() and GROUP BY.

Expand|Select|Wrap|Line Numbers
  1. SELECT column_name, COUNT(*) as occurence FROM table_name
  2. GROUP BY column_name
  3. ORDER BY occurence DESC
Jan 6 '10 #2

nbiswas
100+
P: 149
Try this

Expand|Select|Wrap|Line Numbers
  1. select Result = animals + '  ' +  CAST(COUNT(animals) as varchar(10))  + '  times'
  2. from AnimalTable
  3. group by animals 
  4. order by COUNT(animals) desc
Output:
Expand|Select|Wrap|Line Numbers
  1. Result
  2. Cow  4  times
  3. Cat  3  times
  4. Rat  2  times
  5. Dog  1  times
  6.  
Jan 7 '10 #3

100+
P: 539
Thank you for your replies experts. Effective =)
Jan 7 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.