Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 2038
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: 2038
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
No new posts Query on secondary index Suja.Sai IMS DB/DC 0 Wed Oct 31, 2018 9:47 pm
No new posts Query regarding STOP REGION XX ABDU... ashek15 IMS DB/DC 11 Fri Oct 19, 2018 10:13 am
No new posts Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm
No new posts Generate SQL query dynamically using ... vnktrrd DB2 7 Tue Aug 28, 2018 8:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us