SQL Aggregate functions with groupings

Question

I need to create some checks to make sure that students are enrolled in the correct courses with the correct number of units. Here is my SQL at the moment.

  SELECT StudentID
  ,AssessmentCode
  ,BoardCode
  ,BoardCategory
  ,BoardUnits
  ,sum(cast(boardunits as int)) over (partition by studentid,boardcategory) as UnitCount
  ,Count(boardcategory) over (partition by studentid)  as SubjectCount
FROM uvNCStudentSubjectDetails
where fileyear = 2015 
and filesemester = 1
and studentyearlevel = 11 
and StudentIBFlag = 0
order by Studentnameinternal,BoardCategory

This gives me the following info...

StudentID   AssessmentCode  BoardCode   BoardCategory   BoardUnits  UnitCount   SubjectCount
61687       11TECDAT        11080             A             2          11           7
61687       11PRS1U         11350             A             1          11           7
61687       11MATGEN        11235             A             2          11           7
61687       11LANGRB        11870             A             2          11           7           
61687       11ENGSTD        11130             A             2          11           7
61687       11GEOGEO        11190             A             2          11           7
64549       11TECIND        11200             A             2          10           7
64549       11SCIPHY        11310             A             2          10           7
64549       11SCIEAE        11100             A             2          10           7
64549       11MATGEN        11235             A             2          10           7
64549       11ENGSTD        11130             A             2          10           7
64549       11TECHOS        26501             B             2          2            7
64549       11MUSDRS        63212             C             1          1            7
45461       11ECOECO        11110             A             2          13           7
45461       11ENGADV        11140             A             2          13           7
45461       11HISMOD        11270             A             2          13           7
45461       11HISLST        11220             A             2          13           7
45461       11MATMAT        11240             A             2          13           7
45461       11PRS1U         11350             A             1          13           7
45461       11SCIBIO        11030             A             2          13           7

Note for the first student, I have a count of Category A subject Units (11 in total) He is only doing Category A subjects. For the second student, he has 10 units of Category A subjects, he is doing 1 Category B subject worth 2 units and one category C subject worth 1 unit. the final student just has 13 Category A units.

Now what I would really like is something like this...!

StudentID   Sum A Units Sum B Units Sum C Units Sum A Units + Sum B Units   Count of Subjects
61687          11           0           0                  11                 7
64549          10           2           1                  12                 7
45461          13           0           0                  13                 7

So I would like some aggregated functions with a student grouped onto only 1 row and the sum of his different units as separate fields. I would also like a field which sums the Category A and B Units and also a field which gives a count of the total number of subjects they are doing. I could then use this data to set up some warning messages if a student is not doing the correct number of A or B Units etc

I have played around with common table expressions, subqueries etc but am not really sure what I am doing and am not sure which is the correct way about getting the data in the form I want.

Is anyone able to help?

Balthazar · Accepted Answer · 2015-02-19T14:08:10.067

SELECT
STUDENTID,
SUM(CASE BOARDCATEGORY WHEN 'A' THEN 1 ELSE 0 END) AS SUM_A_UNITS,
SUM(CASE BOARDCATEGORY WHEN 'B' THEN 1 ELSE 0 END) AS SUM_B_UNITS,
SUM(CASE BOARDCATEGORY WHEN 'C' THEN 1 ELSE 0 END) AS SUM_C_UNITS,
SUM(CASE BOARDCATEGORY WHEN 'A' THEN 1 WHEN 'B' THEN 1 ELSE 0 END) AS SUM_A_UNITS+SUM_B_UNITS,
COUNT(BOARDCODE) AS COUNT_OF_SUBJECTS
FROM (
    SELECT StudentID
    ,AssessmentCode
    ,BoardCode
    ,BoardCategory
    ,BoardUnits
    ,sum(cast(boardunits as int)) over (partition by studentid,boardcategory) as UnitCount
    ,Count(boardcategory) over (partition by studentid)  as SubjectCount
    FROM uvNCStudentSubjectDetails
    where fileyear = 2015 
    and filesemester = 1
    and studentyearlevel = 11 
    and StudentIBFlag = 0
    order by Studentnameinternal,BoardCategory
)
GROUP BY STUDENTID;

Wrapped your SQL statement in the solution, so that you can see what the solution does straight away.

Use SUM and CASE (i.e. SUM only when a condition is met).

Great - thanks so much - just what I was after. I had to change things a bit as I was after the sum of the units and most subjects are worth 2 units each, not the count. The sums now look like - SUM(CASE BOARDCATEGORY WHEN 'A' THEN BoardUnits ELSE 0 END) AS SUM_A_UNITS. I also had to remove the 'order by' from the SubQuery and use it after the group by statement at the bottom. Thanks again for your help. — David Phillips, Feb 20 '15 at 00:48
Glad I could assits, and that you were able to come to a final solution. — Balthazar, Feb 21 '15 at 06:23

SQL Aggregate functions with groupings

1 Answers1