CODEWITHSUNDEEP
sqlsql-server
snadell
snadell

Reputation: 33

SQL Server case statement in select clause

I have a query which gives me StudentId and PercentageScored in Exams which the Student attended, a student can attend multiple exams

StudentId | PercentageScored
    101         82
    102         57
    101         69
    103         71
    103         42

Below is a sample query, my actual query looks similar to the below.

Select s.StudentId, m.[PercentageScored]
FROM dbo.[Student] S
Inner join dbo.[Marks] m
ON S.[StudentId] = m.[StudentId]
WHERE S.[StudentGroup] = 12 AND S.[Active] = 1

Now i need to add some logic so that my output looks like below

StudentId  | FirstClass | SecondClass | ThirdClass
   101
   102
   103
   104

If the students PercentageScored is above 80% then 1st class, PercentageScored between 60 to 80 % then 2nd class,PercentageScored below 60% then 3rd class.. I need to give the counts, for a given student how many times he scored more than 80%, how many times between 60 - 80%, how many times below 60%

Upvotes: 0

Views: 59

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Using conditional aggregation:

SELECT
    s.StudentId,
    COUNT(CASE WHEN m.[PercentageScored] > 80 THEN 1 END) AS FirstClass,
    COUNT(CASE WHEN m.[PercentageScored] > 60 AND
                    m.[PercentageScored] <= 80 THEN 1 END) AS SecondClass,
    COUNT(CASE WHEN m.[PercentageScored] <= 60 THEN 1 END) AS ThirdClass
FROM dbo.[Student] s
INNER JOIN dbo.[Marks] m
    ON s.[StudentId] = m.[StudentId]
WHERE
    s.[StudentGroup] = 12 AND s.[Active] = 1
GROUP BY
    s.StudentId;

Upvotes: 2

Related Questions