SQL Server : different aggregate functions conditional on column values

Question

I have a table like this (significantly simplified):

|student_id|Courses| grades |
+----------+-------+--------+
|    001   |  Math |   95   |
|    001   |  Math |   83   |
|    001   | Others|   33   |
|    002   |  Math |   92   |
|    002   | Others|   12   |
|    002   | Others|   72   |

What I want:

Count of 'Math'
Minimum of 'Others'
Grouped by student_id

Desired outcome:

|student_id|Num_math|min_others|
+----------+--------+----------+
|    001   |    2   |    33    |
|    002   |    1   |    12    |

Or, [this one](https://stackoverflow.com/questions/1288058/conditional-count-on-a-field) but the idea is the same. — S3S, Nov 26 '18 at 21:52

Zeki Gumus · Answer 1 · 2018-11-26T22:03:09.333

1

Like Gordon said, you need to use GROUP BY, COUNT(), CASE AND MIN. This is what you want:

SELECT student_id
        ,COUNT(CASE WHEN Courses='Math' THEN grades ELSE NULL END) Math
        ,MIN(CASE WHEN Courses='Others' THEN grades ELSE NULL END) Others
FROM Student
GROUP BY student_id

edited Nov 26 '18 at 22:03

answered Nov 26 '18 at 21:58

Zeki Gumus

1,484
7
14

That first one has to be `sum`. 0 is still a value and will be counted by `count`. – Donnie Nov 26 '18 at 22:01
you are right, sum or count with null can work. I have updated. thanks for double check. – Zeki Gumus Nov 26 '18 at 22:04

score 1 · Accepted Answer · answered Nov 26 '18 at 21:58

Use GROUP BY student_id and conditional SUM and MIN:

   SELECT 
    student_id, 
    SUM(CASE Courses WHEN 'Math' THEN 1 ELSE 0 END) AS  Num_math,
    MIN(CASE Courses WHEN 'Others' THEN grades ELSE NULL END) AS min_others
   FROM tablename
    GROUP BY student_id

SQL Server : different aggregate functions conditional on column values

2 Answers2