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 sql Query

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

New User


Joined: 20 Sep 2006
Posts: 23
Location: Chennai

PostPosted: Mon Mar 31, 2014 4:19 am    Post subject: Need sql Query
Reply with quote

Hi ,

Table A.
Market User-id
US G1
US G2
US G3
UK A1
UK A2
UK A3


Table B.
User-ID Product
G1 AAA
G1 BBB
G2 AAA
G3 CCC
G3 BBB
A1 SSS
A2 AAA
A2 SSS
A3 CCC
A3 222


need how many user are having more than one product and having only one product according to the market wise.

Could pls help on this query.

Thanks
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Mar 31, 2014 12:54 pm    Post subject:
Reply with quote

Not sure about the requirements, but this is a solution to my interpretation of the requirement
Code:
Select a.market
     , B2.Numprod
     , count(*) as NumUser
  from TabA
     , (Select B.UserId, count(*) as Numprod
          from TabB B
         group by B.UserId) B2
 where a.UserID = b2.UserID
 group by a.market,B2.Numprod
Back to top
View user's profile Send private message
itdsen

New User


Joined: 20 Sep 2006
Posts: 23
Location: Chennai

PostPosted: Tue Apr 01, 2014 4:20 am    Post subject:
Reply with quote

Thank GuyC,

working fine..

Expected results is

Market Total user User with one Product User with Multiple Product
------- ---------- -------------------------- -------------------------
US 3 1 2
UK 3 1 3



Thanks!
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Apr 01, 2014 5:37 am    Post subject:
Reply with quote

Quote:
need how many user are having more than one product and having only one product according to the market wise.


GuyC,

Do we need to add HAVING condition?

Code:
Select B.UserId, count(*) as Numprod
          from TabB B
         group by B.UserId
         having count(*) > 1 <<< To get user's for more than 1 product code>>>


Output of your query would produce,

Code:
US 2 2
US 1 1
UK 1 1
UK 2 2
Back to top
View user's profile Send private message
itdsen

New User


Joined: 20 Sep 2006
Posts: 23
Location: Chennai

PostPosted: Tue Apr 01, 2014 12:30 pm    Post subject:
Reply with quote

Yes correct ! this query will produce output based on number of user associate with product count but I need to combine all the user count for more than one product.


Table A.
Market User-id
US G1
US G2
US G3
UK A1
UK A2
UK A3


Table B.
User-ID Product
G1 AAA
G1 BBB
G2 AAA
G3 CCC
G3 BBB
G3 CCC
G3 AAA
G3 222
A1 SSS
A2 AAA
A2 SSS
A3 CCC
A3 222







this query will produce output as...

US 1 1
US 2 1
US 4 1
..... ect


but required out is

US 1 1
US more than 1 2
want to sum number of user with more than one product.

Thanks.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Thu Apr 03, 2014 6:00 am    Post subject:
Reply with quote

can you try below query and let us know

Code:
SELECT A.Market                                             as Market          ,
       Count(*)                                             As Total_User      , 
       SUM(CASE(WHEN innq.total_user = 1 then 1 Else 0)END) as single_prod_user,
       SUM(CASE(WHEN innq.total_user > 1 then 1 Else 0)END) as multi_prod_user
from tablea A, 
(SELECT user-id ,count(*) as total_user from  tableb group by user-id) AS innq

WHERE
A.User_id = Innq.user_id
GROUP BY A.Market
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
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
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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