Below is the INPUT Table having student and his/her two interests of learning different subjects.
| Student | Int1 | Int2 |
|---|---|---|
| 1 | DS | Networks |
| 2 | OS | DS |
| 3 | DS | OS |
| 4 | Networks | DB |
| 5 | OS | Networks |
| 6 | DB | DS |
| 7 | Networks | OS |
| 8 | DB | OS |
Need to find domain of interest and number of students interested in it.
Output Should be
| Interest | Total Students |
|---|---|
| DS | 4 |
| OS | 5 |
| DB | 3 |
| Networks | 4 |
First I join the 2 result in a temp table and count by the temp table.
;with ETA(ref,count1) as
(select Int1, count(Int1) from [yourtable] group by Int1
union all
select Int2, count(Int2) from [yourtable] group by Int2)
select count1, count(count1) from ETA group by count1
You can query data from the two columns,then using UNION ALL to put them together,finally using GROUP BY to statistics data
SELECT count(c.student) as cnt, c.inte
FROM
(
SELECT `int1` as inte,student FROM course
union all
SELECT `int2` as inte,student FROM course
) c
GROUP BY c.inte
ORDER BY c.inte
Test result
| cnt | inte |
|---|---|
| 3 | DB |
| 4 | DS |
| 4 | Network |
| 5 | OS |