CODEWITHSUNDEEP
mysqlsql
Developer
Developer

Reputation: 695

How to use count in SELECT query to create a view from multiple tables?

I have a MySQL table which gives details of students in different schools (shown below). SchoolName defines a unique school. Similarly, StudentName and StudentId together uniquely define a student.

| SchoolName | StudentName |  StudentId |

| ABC        | Tom         |  147852    |
| ABC        | Nix         |  258963    |
| ABC        | Bob         |  898596    |
| XYZ        | Ross        |  369369    |
| XYZ        | Jimmy       |  147852    |

I have another table, which shows a course taken by a student (shown below). Here, a character 'X' denotes that a student has taken that course. Blank means he has not taken that course. Assume that a student can take only 1 course.

| StudentName | StudentId | StudiesPhysics |  StudiesMaths |  StudiesChemistry  |

| Tom         | 147852    |                |  X            |                    |
| Nix         | 258963    |   X            |               |                    |
| Bob         | 898596    |                |  X            |                    |
| Ross        | 369369    |                |               |   X                |
| Jimmy       | 147852    |  X             |               |                    |

I want a view which shows number of students studying each course in each school (example shown below). Please help me in writing a MySql query to get this desired result.

| SchoolName | CountPhysics |  CountMaths |  CountChemistry  |

| ABC        | 1            |  2          | 0                |
| XYZ        | 1            |  0          | 1                |

Upvotes: 1

Views: 61

Answers (2)

mohan111
mohan111

Reputation: 8865

using simple Count and Group By 

DECLARE @Table1 TABLE 
    ( SchoolName  varchar(3),  StudentName  varchar(5),  StudentId  int)
;

INSERT INTO @Table1
    ( SchoolName ,  StudentName ,  StudentId )
VALUES
    ('ABC', 'Tom', 147852),
    ('ABC', 'Nix', 258963),
    ('ABC', 'Bob', 898596),
    ('XYZ', 'Ross', 369369),
    ('XYZ', 'Jimmy', 147852)
;




 DECLARE @Table2 TABLE 
    ( Name  VARCHAR(10) ,  Id  INT ,  Physics  VARCHAR(10),  Maths  VARCHAR(10),  Chemistry  VARCHAR(10))
;

INSERT INTO @Table2
    ( Name ,  Id ,  Physics ,  Maths ,  Chemistry )
VALUES
    ('Tom',147852,NULL,'X',NULL),
    ('Nix',258963,'X',NULL,NULL),
    ('Bob',898596,NULL,'X',NULL),
    ('Ross',369369,NULL,NULL,'X'),
    ('Jimmy',147852,'X',NULL,NULL)


    select T.SchoolName,
    COUNT(TT.Physics)Physics,
    COUNT(TT.Maths)Maths,
    COUNT(TT.Chemistry)Chemistry from @Table1 T
    INNER JOIN @Table2 TT
    ON T.StudentId = TT.Id AND TT.Name = T.StudentName
    GROUP BY T.SchoolName

Upvotes: 0

Sandesh
Sandesh

Reputation: 1044

SELECT
SN.SchoolName,
SUM(CASE WHEN C.StudiesPhysics='X' THEN 1 ELSE 0 END ) AS CountPhysics,
SUM(CASE WHEN C.StudiesMaths='X' THEN 1 ELSE 0 END ) AS CountMaths,
SUM(CASE WHEN C.StudiesChemistry='X' THEN 1 ELSE 0 END ) AS CountChemistry
FROM SchoolName AS SN
LEFT JOIN Course AS C
ON SN.StudentId=C.StudentID
GROUP BY SN.SchoolName

Upvotes: 4

Related Questions