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
 

 

Need help regarding suitable query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sanjay sahu

New User


Joined: 18 Feb 2014
Posts: 3
Location: India

PostPosted: Mon Mar 17, 2014 9:34 pm    Post subject: Need help regarding suitable query
Reply with quote

Please help me to find the suitable query to extract the result

I have a payment table like below:-

Code:
Customer-Id      BillCompany       Payment
--------------       -------------         -----------
111111              Reliance             100
222222              Airtel                  200
333333              M&S                   50
444444              Digits                 5000
111111              Airtel                 20
444444              Reliance             50
333333              Airtel                 2000 
111111              Reliance            100
111111              Reliance            100
222222              Airtel                200
444444              Digits                333
111111              Idea                 898
222222              Vodafone           253
111111              BSNL                 10

I want to list the Customer-id and the number of Billcompany assigned to customer.

My output should be like:-

As Customer 111111 has total 4 bill company added(Reliance, Airtel, Idea, BSNL), Customer 222222 has total 2 bill comapny added(Airtel, Vodafone)...

Code:
Customer-Id        Total no of Bill company

111111                  4
222222                  2
333333                  2
444444                  2
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 18, 2014 12:26 am    Post subject:
Reply with quote

Hello and welcome to the forum,

First, when posting info that needs to retain alignment, you should use the Code tag.

What have you tried and what happened?

We expect members to try to code their query and show what happens if it is not what was needed.

There are many exanples here in the DB2 part of the forum. Which is where this should have been posted. . . I'll relocate the topic for you.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1653
Location: NY,USA

PostPosted: Tue Mar 18, 2014 12:47 am    Post subject:
Reply with quote

As suggested by Dick please do a initial search on the forum and you will get plenty of relevant examples, however please have a look at the information,

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.qmf9.doc.ref%2Fsumcon.htm

Make a use of Group By and Count (*) to get results.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Fri Mar 21, 2014 1:01 pm    Post subject:
Reply with quote

Code:
...
count(distinct billcompany)
...
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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