|
View previous topic :: View next topic
|
| Author |
Message |
itdsen
New User
Joined: 20 Sep 2006
Posts: 23
Location: Chennai
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
itdsen
New User
Joined: 20 Sep 2006
Posts: 23
Location: Chennai
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
itdsen
New User
Joined: 20 Sep 2006
Posts: 23
Location: Chennai
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
