How to count column with condition in SQL Server

Question

Need help to count pass subject level wise.

The problem is that below query count all the pass subject without checking level and show total count in all level as shown in STUDKEY='0100100003' the student(0100100003) pass 1 subject in level 1, 1 in level 2 and 1 in level 3

SELECT 
    s.STUDKEY,
    sb.LEVEL_ID,
    COUNT(sb.SUBJECT_ID) [No of papers], 
    (SELECT count(t.SUBJECT_ID) 
     FROM tab_exam_forms_tran t 
     INNER JOIN tab_exam_forms f1 ON f1.EXAM_FORM_ID = t.EXAM_FORM_ID 
     WHERE t.PASS = 'Y' AND f1.studkey = s.studkey) AS "Pass Paper"
FROM
    TAB_SYLLABUS_SUBJECTS sb 
INNER JOIN 
    TAB_STUDENTS s ON s.SYLLABUS_ID = sb.SYLLABUS_ID
INNER JOIN 
    tab_exam_forms f ON f.studkey=s.studkey and f.session_id='46'
GROUP BY
    s.STUDKEY, sb.LEVEL_ID
ORDER BY
    s.STUDKEY, sb.LEVEL_ID

Current output

STUDKEY    |LEVEL_ID| No of |  Pass
           |        |papers | papers
0100100000 | 1      |1      |0
0100100000 | 2      |2      |0
0100100000 | 3      |2      |0
0100100001 | 1      |1      |0
0100100001 | 2      |2      |0
0100100001 | 3      |2      |0
0100100002 | 1      |1      |0
0100100002 | 2      |2      |0
0100100002 | 3      |2      |0
0100100003 | 1      |1      |3
0100100003 | 2      |2      |3
0100100003 | 3      |2      |3
0100100004 | 1      |1      |0
0100100004 | 2      |2      |0
0100100004 | 3      |2      |0

Expecting output

STUDKEY    |LEVEL_ID| No of |  Pass
           |        |papers | papers
0100100000 | 1      |1      |0
0100100000 | 2      |2      |0
0100100000 | 3      |2      |0
0100100001 | 1      |1      |0
0100100001 | 2      |2      |0
0100100001 | 3      |2      |0
0100100002 | 1      |1      |0
0100100002 | 2      |2      |0
0100100002 | 3      |2      |0
0100100003 | 1      |1      |1
0100100003 | 2      |2      |1
0100100003 | 3      |2      |1
0100100004 | 1      |1      |0
0100100004 | 2      |2      |0
0100100004 | 3      |2      |0

Answer 1

You need to revise your table structure.

There is no distinction for papers as per level in table tab_exam_forms_tran

You are using Level ID in table TAB_SYLLABUS_SUBJECTS, but it has no linkup with 'tab_exam_forms' and 'ab_exam_forms_tran'

In my opinion :

1. You need to have Level ID in tables 'tab_exam_forms' and 'ab_exam_forms_tran' and use that column in joins

   OR

2. Primary id of table TAB_SYLLABUS_SUBJECTS as foreign key in tables 'tab_exam_forms' and 'ab_exam_forms_tran' and use it in joins

Answer 2

In your original query, You can add an additional filter LEVEL_ID = s.LEVEL_ID. For this you will have to join with TAB_STUDENTS and TAB_SYLLABUS_SUBJECTS in your co-related sub query like this

SELECT 
    s.STUDKEY,
    sb.LEVEL_ID,
    COUNT(sb.SUBJECT_ID) [No of papers], 

        (SELECT count(DISTINCT t.SUBJECT_ID) FROM tab_exam_forms_tran t 
        INNER JOIN tab_exam_forms f1 ON f1.EXAM_FORM_ID=t.EXAM_FORM_ID 
        INNER JOIN TAB_STUDENTS s1 ON f1.studkey=s1.studkey and f.session_id='46'
        INNER JOIN TAB_SYLLABUS_SUBJECTS sb1 on s1.SYLLABUS_ID = sb1.SYLLABUS_ID
        WHERE t.PASS='Y' AND f1.studkey=s.studkey AND sb1.LEVEL_ID =  s.LEVEL_ID) AS "Pass Paper"

    from TAB_SYLLABUS_SUBJECTS sb 
    INNER JOIN TAB_STUDENTS s on s.SYLLABUS_ID = sb.SYLLABUS_ID
    INNER JOIN tab_exam_forms f on f.studkey=s.studkey
    group by s.STUDKEY,sb.LEVEL_ID
    order by s.STUDKEY,sb.LEVEL_ID

OR, You can do it without the sub query as well. Just use a CASE in your parent query like this.

SELECT 
    s.STUDKEY,
    sb.LEVEL_ID,
    COUNT(DISTINCT sb.SUBJECT_ID) [No of papers], 
    SUM(CASE WHEN t.PASS='Y' THEN 1 ELSE 0 END) "Pass Paper"
    from TAB_SYLLABUS_SUBJECTS sb 
    INNER JOIN TAB_STUDENTS s on s.SYLLABUS_ID = sb.SYLLABUS_ID
    INNER JOIN tab_exam_forms f on f.studkey=s.studkey and f.session_id='46'
    INNER JOIN tab_exam_forms_tran t ON t.EXAM_FORM_ID = f.EXAM_FORM_ID
    group by s.STUDKEY,sb.LEVEL_ID
    order by s.STUDKEY,sb.LEVEL_ID