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

Mysql – find count of students based on interest

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

2

Answers


  1. 0

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

    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))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search