SQLTeam.com | Weblogs | Forums

Student percentage who passed in at least one subject

table:
STU_ID | MARKS | SUBJECT
1 | 35 | Maths
1 | 36 | Physics
2 | 38 | Maths
2 | 30 | Physics
4 | 36 | Maths
4 | 36 | Physics
5 | 33 |Maths
5 | 32 |physics

output: total student percentage passed in at least one subject, here pass mark is 35.
Please help me

hi hope this helps :slight_smile:

DROP TABLE #Sample 
GO 

CREATE TABLE #Sample
(
STU_ID INT ,  MARKS INT ,  SUBJECT VARCHAR(20)
)
GO 


INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 1 , 35 ,'Maths'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 1 , 36 ,'Physics'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 2 , 38 ,'Maths'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 2 , 30 ,'Physics'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 4 , 36 ,'Maths'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 4 , 36 ,'Physics'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 5 , 33 ,'Maths'
INSERT INTO #Sample    (         STU_ID         , MARKS         , SUBJECT     ) SELECT 5 , 32 ,'Physics'

SELECT * FROM #Sample AS S
GO 


SELECT
    CAST((s.count_who * (1.0) / s2.count_all * (1.0)) * 100 AS INT) AS Percentage_Student
FROM
(   SELECT
        COUNT(DISTINCT STU_ID) AS count_who
    FROM
        #Sample
    WHERE
        MARKS > 35) s
, (   SELECT
            COUNT(DISTINCT STU_ID) AS count_all
      FROM
            #Sample) s2;

image