Агрегатные функции SQL с группировками

Мне нужно создать некоторые проверки, чтобы убедиться, что студенты зачислены на правильные курсы с правильным количеством единиц. Вот мой SQL на данный момент.

  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

Это дает мне следующую информацию...

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

Примечание для первого ученика: у меня есть количество предметных единиц категории А (всего 11). Он занимается только предметами категории А. Для второго ученика у него 10 единиц предметов категории А, он занимается 1 предметом категории В стоимостью 2 единицы и одним предметом категории С стоимостью 1 единица. у последнего студента всего 13 единиц категории А.

Теперь то, что я действительно хотел бы, это что-то вроде этого ...!

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

Поэтому я хотел бы, чтобы некоторые агрегированные функции со студентом были сгруппированы только в 1 строку, а сумма его различных единиц - в виде отдельных полей. Я также хотел бы поле, которое суммирует единицы категорий A и B, а также поле, которое дает подсчет общего количества предметов, которые они делают. Затем я мог бы использовать эти данные для настройки некоторых предупреждающих сообщений, если учащийся не выполняет правильное количество модулей A или B и т. д.

Я играл с общими табличными выражениями, подзапросами и т. д., но не совсем уверен, что делаю, и не уверен, какой правильный способ получения данных в нужной мне форме.

Кто-нибудь может помочь?


sql partition aggregate
person David Phillips    schedule 19.02.2015    source источник
comment
какие dbms вы используете?   -  person Mahesh    schedule 19.02.2015

Ответы (1)


arrow_upward
0
arrow_downward 

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;

Заключил ваш оператор SQL в решение, чтобы вы могли сразу увидеть, что делает решение.

Используйте SUM и CASE (т.е. SUM только при выполнении условия).

person Balthazar    schedule 19.02.2015
comment
Отлично - большое спасибо - как раз то, что мне нужно. Мне пришлось немного изменить ситуацию, так как я был после суммы единиц, а большинство предметов стоят по 2 единицы каждый, а не в счет. Теперь суммы выглядят так: SUM(CASE BOARDCATEGORY WHEN 'A' THEN BoardUnits ELSE 0 END) AS SUM_A_UNITS. Мне также пришлось удалить «упорядочить по» из подзапроса и использовать его после оператора group by внизу. В очередной раз благодарим за помощь. - person David Phillips; 20.02.2015
comment
Рад, что смог, задницы, и что вы смогли прийти к окончательному решению. - person Balthazar; 21.02.2015