Home » SQL & PL/SQL » SQL & PL/SQL » Finding most frequently occurring value
Finding most frequently occurring value [message #214284] Mon, 15 January 2007 12:18 Go to next message
jason_c_lemay
Messages: 2
Registered: July 2006
Junior Member
Hello,
I have a simple table, called NAME_TABLE with millions of rows. This table's structure is as follows:

NAME,
ADDRESS,
CITY,
STATE,
ZIPCODE

NOTE: All columns are defined as varchar2(50)

I would like to create a query that returns the value in the 'NAME' column when it is the most frequently occurring value in the table, plus the number of occurrences it contains in the table.

For example, if 'JOHN' occurred 50,000 times in the table and that was the most frequently occurring name, I would like to return:

JOHN 50000

Also, is there an easy way to list, say, the 10 most frequently occurring names?


Thank you in advance,
Jay
Re: Finding most frequently occurring value [message #214290 is a reply to message #214284] Mon, 15 January 2007 12:55 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Most frequently occurring:

select name, cnt count
	from (
	select name, count(*) cnt
	from name_table
	group by name
	order by 2 desc)
where rownum = 1;

Top 10:
select name, cnt count
	from (
	select name, count(*) cnt
	from name_table
	group by name
	order by 2 desc)
where rownum <= 10;
Previous Topic: Column justify not working
Next Topic: execute procedure
Goto Forum:
  


Current Time: Sun Dec 04 14:45:36 CST 2016

Total time taken to generate the page: 0.16254 seconds