SQL sort and count duplicate value

I need to count and sort student, how many course they took based on level like this:

ID|NAME |Point1|point 2|point 3|
1 | Tom |     3|   3   |   1   |
2 | Jer |     5|   0   |   6   |

I have 3 table student,Course,Registration

Member: student_id,student_name

Subject: subject_id,point_score

Registration: student_id,subject_id

What I currently have:

Select Max(m.student_id) AS 'ID', student_name AS 'Name',COUNT(point_score) as'level 1',
( select count(point_score) from Subject where point_score=2)as'point 2',
(select COUNT(point_score) from subject where point_score=3) as 'level 3'
from Student m inner join Registration r on m.student_id = r.student_id
inner join subject c on c.subject_id=r.subject_id
where point_score =1
group by student_name
Go

The problem with it, is that it only counts point 1 correctly and not point 2 and 3 and it doesn’t display the user if they do not take any subject like this:

ID   NAME  Point1 Point2 Point3
1   |Tom  |     0|     0|    0

Answer

Your sub-selects in the result columns need to join through the Registration table too.

To see students who don’t take any subject, change the joins in the main query to left outer joins, or, better yet, process point 1 the same way you process point 2 and 3.

Using sub-queries

SELECT m.student_id AS 'ID'
     , m.student_name AS 'Name'
     , ( SELECT COUNT(*)
           FROM Registration r
           JOIN Subject c ON c.subject_id = r.subject_id
          WHERE r.student_id = m.student_id
            AND c.subject_level = 1
       ) AS 'point 1'
     , ( SELECT COUNT(*)
           FROM Registration r
           JOIN subject c ON c.subject_id = r.subject_id
          WHERE r.student_id = m.student_id
            AND c.point_score = 2
       ) AS 'point 2'
     , ( SELECT COUNT(*)
           FROM Registration r
           JOIN Subject c ON c.subject_id = r.subject_id
          WHERE r.student_id = m.student_id
            AND c.point_score = 3
       ) AS 'point 3'
  FROM Student m

Using outer joins and group by

SELECT m.student_id AS 'ID'
     , m.student_name AS 'Name'
     , SUM(CASE WHEN c.point_score = 1 THEN 1 END) AS 'Point 1'
     , SUM(CASE WHEN c.point_score = 2 THEN 1 END) AS 'Point 2'
     , SUM(CASE WHEN c.point_score = 3 THEN 1 END) AS 'Point 3'
  FROM Student m
  LEFT JOIN Registration r
  LEFT JOIN Subject c ON c.subject_id = r.subject_id
 GROUP BY m.student_name
        , m.student_id