find count of students based on interest-CodePudding

Write a sql query that prints domain interest and number students who are interested in that domain.

 ------ ---------- ---------- 
| sno  | sub1     | sub2     |
 ------ ---------- ---------- 
| s1   | ds       | networks |
| s2   | os       | ds       |
| s3   | ds       | os       |
| s4   | networks | db       |
| s5   | os       | networks |
| s6   | db       | ds       |
| s7   | networks | os       |
| s8   | db       | os       |
 ------ ---------- ----------

Expected output:

 ---------- ------------- 
| sub1     |Totalstudents|
 ---------- ------------- 
| db       |           3 |
| ds       |           4 |
| networks |           4 |
| ob       |           5 |
 ---------- -------------

This is what he/she has tried:

select sub1, count(sub1) 
from student 
group by sub1;

then tried subquery as

select a.sub1
     , count(a.sub1) 
      ,count(b.sub1) 
from student a, student b 
where a.sub1=b.sub2 
group by a.sub1, b.sub2;

CodePudding user response：

Here's my take on this, obviously more clumsy than UNION ALL (I'm learning SQL, so any comments are more than welcome).

SELECT 
    a.sub AS Subject, a.c   b.c AS Count
FROM
    ((SELECT 
        sub1 AS sub, COUNT(sub1) AS c
    FROM
        students
    GROUP BY sub1
    ORDER BY sub1) AS a
    JOIN (SELECT 
        sub2 AS sub, COUNT(sub2) AS c
    FROM
        students
    GROUP BY sub2
    ORDER BY sub2) AS b USING (sub))

CodePudding user response：

You could utilize UNION ALL and GROUP BY:

SELECT 
  sub AS sub1, 
  COUNT(*) AS Totalstudents 
FROM 
  (
    SELECT 
      sub1 AS sub 
    FROM 
      students 
    UNION ALL 
    SELECT 
      sub2 AS sub 
    FROM 
      students
  ) AS t 
GROUP BY 
  sub;

Output:

 ---------- --------------- 
| sub1     | Totalstudents |
 ---------- --------------- 
| db       |             3 |
| ds       |             4 |
| networks |             4 |
| os       |             5 |
 ---------- ---------------