Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
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    Post subject: count of largest n of recs with the same customer in a table
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    Post subject: Reply to: count of largest n of recs with the same customer
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Can you write 2 recs on SORTOUT when ... Sysaron DFSORT/ICETOOL 6 Thu Feb 23, 2017 12:07 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us