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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts For each given key value, how to keep... Div Grad DFSORT/ICETOOL 7 Fri Nov 11, 2016 11:38 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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