sql - How to perform "Select Count" with complicated "Where" statement to compute co-occurrences? -

sql - How to perform "Select Count" with complicated "Where" statement to compute co-occurrences? -

let's have illustration declare concern: suppose have table (tags) has 2 columns this

userid -------------------------------- tag 1 -------------------------------------- sql 1 -------------------------------------- select 1 -------------------------------------- db 2 -------------------------------------- sql 2 -------------------------------------- programming 2 -------------------------------------- code 2 -------------------------------------- software 3 -------------------------------------- code 4 -------------------------------------- sql 4 -------------------------------------- code

i need count distinct co-occurrences each tag based on userid

so, output should (with order co-occurrences desc):

tag -------------------------------- co-occurrences --------------------------------------------- sql --------------------------------------- 5 programming ------------------------------- 3 code -------------------------------------- 3 software ---------------------------------- 3 select ------------------------------------ 2 db ---------------------------------------- 2

this example..

how can create select statement can this?

i came 1 way 1 specific tag:

select count (distinct (tag)) - 1 co_occurrences tags tag not null , userid in ( select userid tags tag = 'sql')

is possible alter above statement create general tags in table?

select t2.tag, count (distinct (t1.tag)) - 1 co_occurrences tags t1 inner bring together tags t2 on t1.userid = t2.userid grouping t2.tag order count (distinct (t1.tag)) desc

sql select count