0

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
Shadow
  • 33,525
  • 10
  • 51
  • 64
Utkarsh Kendre
  • 1
  • Just a small warning, [Int1](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-I:~:text=INT%20(R)-,INT1,-(R)), and "Int2" are reserved words in MySQL. Using Reserved words for column names is something which should be avoided – Luuk Sep 17 '22 at 09:12

2 Answers2

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
Yat Fei Leong
  • 751
  • 1
  • 6
  • 10
  • [A query normally consists of an SQL statement followed by a semicolon.](https://dev.mysql.com/doc/refman/8.0/en/entering-queries.html#:~:text=A%20query%20normally%20consists%20of%20an%20SQL%20statement%20followed%20by%20a%20semicolon.), why do you start with a semi-colon? – Luuk Sep 17 '22 at 08:38
  • If u query from management studio u need to include ; for temp table. If you put in the program, u may exclude the ; Try it yourself – Yat Fei Leong Sep 17 '22 at 08:47
  • "management studio" is that not some Microsoft tool so mssql? And, even in management studio, the semi-colon is not needed. – Luuk Sep 17 '22 at 09:16
  • probably I am using the earlier version. The query simply won't run without the semicolon. I tested the query before I posted the answer. – Yat Fei Leong Sep 17 '22 at 09:20
  • see: [When should I use semicolons in SQL Server?](https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server), which was asked and answered 13 years ago. – Luuk Sep 17 '22 at 09:21
  • Exactly Luke. Refer to the first answer. did you realise I am using CTE. From your answer. "There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE)," – Yat Fei Leong Sep 17 '22 at 09:32
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
flyingfox
  • 13,414
  • 3
  • 24
  • 39