IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

count of largest n of recs with the same customer in a table


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sathish_rathinam

New User


Joined: 22 Aug 2005
Posts: 59
Location: india

PostPosted: Thu Jun 26, 2008 4:19 pm
Reply with quote

In a table,there are 3,00,000 records and it has customer id field as one of its fields.
I want to pick up the customer id which is present in large number than all other customer id's using a SQL.
Back to top
View user's profile Send private message
Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Thu Jun 26, 2008 5:41 pm
Reply with quote

Hi

Select cust_id, count(*)
from TABLE_NAME
group by cust_id

It will give you a list with the customer id's and the number of times they occur within the table.
Notice that this is a very expensive SQL statement. It has to perform a full table space scan in order to obtain the data.
Back to top
View user's profile Send private message
ssk1711

New User


Joined: 16 Jun 2008
Posts: 40
Location: bangalore

PostPosted: Thu Jun 26, 2008 6:33 pm
Reply with quote

hi sathish,

try this..

********
select count(column_name) col1,column_name
from table_name
group by column_name
order by col1 desc
fetch first 1 row only
***********
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top