SQL join results from GROUP BY and COUNT in 2 tables

Table 1 (VID is unique)

VID|UID
99 | 11
56 | 12
52 | 11
24 | 13
14 | 13

To count the records by UID,

SELECT count(*),UID from t1 GROUP by UID
------------
count(*)|UID
       2| 11
       1| 12
       2| 13

Table 2 (which is a subset from table 1)

VID
99
52
14

To count the records by UID after mapping with table 1,

SELECT count(*),uid from t1 WHERE vid in (SELECT vid from t2) group by uid
------------
count(*)|UID
       2|11
       1|13

Now can I join these 2 result tables together, based on 2nd result with 1 SQL such that the results are:?

t1Count|t2Count|UID
      2|      2|11
      2|      1|13

What if I want to join an extra table which contains info for UIDs:

UID|info
 11|....
 12|....
 13|....
 14|....
 15|....

such that results are:?

t1Count|t2Count|UID|info
      2|      2|11 |....
      2|      1|13 |....

Answer

Try below query to get desired result.

select t1count,t2count,tab1.UID from (SELECT count(*) as t1count,UID from t1 GROUP by UID
    ) tab1
    inner join 
    (SELECT count(*) as t2Count,uid from t1 WHERE vid in (SELECT vid from t2) group by uid)  tab2
    on tab1.UID=tab2.UID

Advertisement

Answer