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: 1624
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: 1278
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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