self join with count unique in MySQL

user_19452431 0
Viewed 39

I have a table with 2 columns; student IDs and major. Some students have a double major (or more) hence have multiple entries. I want to find the most commonly declared double majors, rank them in descending order, based on the number of students, keeping only double majors that have at least 2 students. example:

StudentID Major
100 Science
100 Maths
200 Engineer
200 Maths
201 Arts
300 Biology
300 Business
401 Science
400 Business
400 Engineer
401 Arts
120 Maths
134 Business

Outcome required

Major1 Major2 Count_of_Students
Science Maths 2
Science Arts 2
Science Business 2
Maths Engineer 2
Engineer Business 2

I have tried many queries of which the closest (logically) was:

SELECT distinct A.Major as Major1, 
       B.Major as Major2, 
       (SELECT COUNT(*) FROM table1 where StudentID = B.StudentID and StudentID = A.StudentID) as Count_of_Students 
FROM Table1 A
inner join Table1 B on A.Major <> B.Major 
having Count_of_Students>=2 
order by Count_of_Students desc;
mysql
1 Answers

Unfortunately MySQL doesn't have PIVOT , which is the first step needed to give the expected result so you have to use:

 select StudentID,
        max(case when ColumnSequence=1 then Major end) Major1,
        max(case when ColumnSequence=2 then Major end) Major2
 from (
        Select StudentID, 
               Major,
               row_number() over(partition by StudentID order by StudentID) ColumnSequence
        from tbl ) as t1
 group by StudentID

Above query will convert rows to columns , if you have more than two values per StudentID add another condition max(case when ColumnSequence=2 then Major end) Major3.

Then use below query to find pairs more than once. Note. I added the following values (201,'Science'), (201,'Maths') to get a count more than once.

Use:

SELECT CONCAT(LEAST(Major1, Major2), ',', GREATEST(Major1, Major2)) as pair, 
       COUNT(*) as unique_pair_repeats
FROM  ( select StudentID,
               max(case when ColumnSequence=1 then Major end) Major1,
               max(case when ColumnSequence=2 then Major end) Major2
        from ( Select StudentID, 
                      Major,
                      row_number() over(partition by StudentID order by StudentID) ColumnSequence
               from tbl 
             ) as t1
       group by StudentID 
       ) as t2 
GROUP BY pair
HAVING count(*) > 1;

Check the demo for more info: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=acf26229e885e5e9bd02a90f7c7b057f

user_164619520
Related