tsql - sql join if value exists in other table then Count it -

tsql - sql join if value exists in other table then Count it -

i have next tables.

table a

userid | key 1 | a 2 | b 3 | a 4 | c 5 |

table b

userid | num1 | 501 | 3002 |3 | 1004 | 20

i have query this

select count(key) cnt, key key <> '' grouping key order cnt desc

the results should this

key | cnt | 2 b | 1 c | 1

what add together joining table b. if userid has value in num in table b, count userid with/num grouped key

here desired results

key | cnt | has num? | 2 | 2 b | 1 | 0 c | 1 | 1

i tried write subquery can't attach main query. subquery this.

select count(distinct userid) num b left outer bring together on b.userid = a.userid num <>'' , key <> '' grouping key

if i'm understanding correctly, you're looking count of keys in table when used userid, , count of number of unique userids in table b both appeared in first table query , had num.

try this:

select a.[key], count(a.[key]) cnt, isnull(sum(b.bcnt), 0) [has num?] #tablea left outer bring together ( select b.userid, 1 #tableb b len(b.num) > 0 grouping b.userid ) b (userid, bcnt) on b.userid = a.userid len(a.[key]) > 0 grouping a.[key]

this query gives results expecting.

tsql sql-server-2008-r2