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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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