Kat Treverson Kat Treverson - 4 years ago 110
SQL Question

Co-occur colors of outfit, SQL

Say I have a data Table, two columns

DATA TABLE

How would I write code in SQL that shows the top two co-occuring colors. So the output should be a table with two columns (color and count) . ex "Green","Red" 3, "Blue" "Blue" 2, "Pink" "Purple" 1. I'm working with a big dataset so I would want something a little simpler to use.

Answer Source

Try this:

declare @t table (
    person varchar(100),
    color varchar(100)
);

insert into @t values
('Bob', 'Green'),
('Bob', 'Red'),
('Mark', 'Blue'),
('Mark', 'Blue'),
('Sally', 'Pink'),
('Sally', 'Purple'),
('Kat', 'Green'),
('Kat', 'Red'),
('Mike', 'Green'),
('Mike', 'Red'),
('Pat', 'Blue'),
('Pat', 'Blue');

with t as (
    select
        t.*,
        row_number() over (partition by person order by color) rn
    from @t t
)
select 
    t1.color color1, 
    t2.color color2,  
    count(*) cnt
from t t1 inner join t t2
on t1.person = t2.person
and t1.rn > t2.rn
group by t1.color, t2.color
order by cnt desc;

Produces:

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download