CODEWITHSUNDEEP
sqlsqlite
jumpman8947
jumpman8947

Reputation: 437

sqlite pair columns that reference similar values

I have a table my_table

sport            score
football         22
basketball       17
baseball         17
baseball         29
hockey           22

I want to pair columns in sport that have the same score

the desired output should be 

sport1      sport2        times_paired
football    hockey        1
basketball  baseball      1

so far in my query i have

select m1.sport as sport1, m1.score as times_paired, m2.sport as sport2 from mytable m1 where(select count(*) as times_paired, m2.sport  from mytable2 m2 where m1.score = m2.score.

This query above does not work. How can i achieve the desired results?

Upvotes: 0

Views: 20

Answers (1)

sticky bit
sticky bit

Reputation: 37472

An inner self join and an aggregation might be what you're looking for.

SELECT t1.sport sport1,
       t2.sport sport2,
       count(*) times_paired
       FROM my_table t1
            INNER JOIN my_table t2
                       ON t2.score = t1.score
                          AND t2.sport > t1.sport
       GROUP BY t1.sport,
                t2.sport;

Upvotes: 3

Related Questions