А, вот интересно, а что будет происходить с функциями типа AVG(), MIN(), MAX(), SUM(), COUNT(), если значение столбца будет содержать значение нашего доброго старого знакомого - NULL? По правилам ANSI/ISO сказано, что "агрегатные функции игнорируют значение NULL"! Вот если честно, ну достал этот NULL, просто сил нет! :) Итак давайте проверим, на примере, что же будет происходить. Дадим вот такой запрос:
SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA) FROM SALESREPS /
Поучаем:
SQL> SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA) 2 FROM SALESREPS 3 / COUNT(*) COUNT(SALES) COUNT(QUOTA) ---------- ------------ ------------ 11 11 10
Странный какой то результат? В чем тут вопрос. Таблица вроде одна, а вот значения в запросе разные. А все дело в том что, одно из полей QUOTA - содержит NULL. От сюда и вся не разбериха. Функция COUNT вида COUNT(поле), при работе, как и было сказано, игнорирует значение NULL, а COUNT(*) просто подсчитывает общее число строк. Ей все равно есть там NULL или нет! :) Как в том мультике - "Он нас всех посчитал!" :) Просто запомните вышесказанное и не будете делать в дальнейшем ошибок! А, вот функции MIN(), MAX() - особо не искажают результат при наличии NULL, так как так же его игнорируют. Но AVG(), SUM() - может при наличии NULL немного ввести вас в заблуждение! Например, посмотрите на следующий запрос:
SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA)) FROM SALESREPS /
Получаем:
SQL> SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA)) 2 FROM SALESREPS 3 / SUM(SALES) SUM(QUOTA) (SUM(SALES)-SUM(QUOTA)) (SUM(SALES-QUOTA)) ---------- ---------- ----------------------- ------------------ 3279,574 3100 179,574 103,589
С SUM(SALES), SUM(QUOTA) все вроде бы ясно, но вот (SUM(SALES)-SUM(QUOTA)) и (SUM(SALES-QUOTA)) как-то странно выглядит. По закону математики собственно, оба выражения должны были вернуть одинаковые результаты, но на поверку это не так! Почему? Давайте подумаем. Выражение SUM(SALES-QUOTA) принимает в качестве аргумента только 10-ть значений, которые не равны NULL. В следствии того, что одно из полей QUOTA, содержит значение NULL. То выражение SUM(значение столбца - NULL) возвращает NULL! SUM его игнорирует! Что и следовало ожидать! Таким образом одно из выражений полностью исключается! Тогда, встает вопрос - какое, выражение верно? А, вот ответ, вас еще более запутает! ОБА! Первое выражение вычисляет именно то, что и означает "СУММА по SALES - СУММА по QUOTA". И соответственно и второе выражение, так же вычисляет именно то, что и должно - "СУММА (SALES-QUOTA)"! Важно понять, что оба выражения справедливы и не содержат ошибок!!! Но значения возвращаемые выражениями отличаются, при наличии в полях таблицы значения NULL! По этому, важно: ХОРОШО ПРЕДСТАВЛЯТЬ, ЧТО ВЫ ХОТИТЕ ПОЛУЧИТЬ В ДАННОМ КОНКРЕТНОМ СЛУЧАЕ ПРИ ПОСТРОЕНИИ ЗАПРОСА! Иначе можно, наделать много ошибок и окончательно в них запутаться!!! :( Вот такой он "кровопийца", этот NULL и это еще не все где мы с ним будем разбираться! Вообще то, я стараюсь по мере, сил (а, сил у меня не меряно!!!) не забивать вам голову сухими формулировками! Но здесь как раз тот случай когда, следует их процитировать, итак:
Вот собственно и все вкратце, что касается NULL и агрегатных функций! Можете проверить все сами! :) Еще один интересный момент, касающийся функции DISTINCT. Ее тоже можно использовать с агрегатными функциями. Например в таких запросах:
1. Сколько различных названий рапортов существует в нашей компании?
SELECT COUNT(DISTINCT TITLE) FROM SALESREPS /
Получаем:
SQL> SELECT COUNT(DISTINCT TITLE) 2 FROM SALESREPS 3 / COUNT(DISTINCTTITLE) -------------------- 5
2. В скольких офисах есть служащие превысившие плановые объемы продаж?
SELECT COUNT(DISTINCT REP_OFFICE) FROM SALESREPS WHERE SALES > QUOTA /
Получаем:
SQL> SELECT COUNT(DISTINCT REP_OFFICE) 2 FROM SALESREPS 3 WHERE SALES > QUOTA 4 / COUNT(DISTINCTREP_OFFICE) ------------------------- 4
Вкратце опишу основные понятия, при работе с DISTINCT и агрегатами. Если вы используете DISTINCT и агрегатную функцию, то ее аргументом может быть только имя столбца, выражение не может быть аргументом. В функциях MIN(), MAX() так же нет смысла использовать DISTINCT! В функции COUNT() в принципе можно использовать DISTINCT, но это требуется не часто. А вот к функции COUNT(*) вообще не применимо DISTINCT, так как она просто подсчитывает число строк! Так же в одном запросе DISTINCT можно употреблять только один раз! Если оно применяется с аргументом, агрегатной функции, его уже нельзя использовать ни с одним другим аргументом! Вот, такие правила для DISTINCT с агрегатами, поэкспериментируйте сами и сможете убедиться!