Question 113660 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to count occurrence of a value in multiple columns

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    DB Fiddle Demo

    Test result

    cnt inte
    3 DB
    4 DS
    4 Network
    5 OS
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search