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

Какой же ответ является правильным ? Оба! Первое выражение вычисляет именно то, что и означает, т.е. сумма по sales минус сумма по quota . И второе выражение также вычисляет именно то, что оно означает, т.е. сумма (sales - quota) . Однако при наличии значений null результаты выражений отличаются.

В стандарте ANSI/ISO определены следующие точные правила обработки значений null в статистических функциях:

если какие-либо из значений, содержащихся в столбце, равны null, при вычислении результата функции они исключаются;

если все значения в столбце равны null, то функции sum{), avg{), min о и мах {) возвращают значение null; функция count () возвращает ноль;

если в столбце нет значений (т.е. столбец пустой), то функции sum {), avg {), min () и max () возвращают значение null; функция count () возвращает ноль;

функция count (*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений null; если строк в таблице нет, эта функция возвращает ноль.

Хотя эта часть стандарта определена достаточно четко, коммерческие СУБД могут выдавать результаты, отличающиеся от стандарта, особенно если все значения, содержащиеся в столбце, равны null или таблица пуста. Прежде чем полагаться на правила, определенные в стандарте, следует проверить свою СУБД.

Удаление повторяющихся строк (предикат DISTINCT)

Из главы 6 вы должны помнить, что предикат distinct указывается в начале списка возвращаемых столбцов и служит для удаления повторяющихся строк из таблицы результатов запроса. С помощью этого предиката можно также указать, что перед применением статистической функции к столбцу из него следует удалить все повторяющиеся значения. Для этого необходимо включить предикат distinct перед аргументом статистической функции сразу же после открывающей круглой скобки.

Ниже приведены два запроса, которые иллюстрируют удаление повторяющихся значений перед применением статистических функций:

Сколько различных должностей существует в нашей компании ?

SELECT COUNT(DISTINCT TITLE) FROM SALESREPS

COUNT(DISTINCT TITLE)

В скольких офисах есть служащие, превысившие плановые объемы продаж?

SELECT COUNT(DISTINCT REP OFFICE) FROM SALESREPS WHERE SALES > QUOTA

COUNT(DISTINCT REP OFFICE)

в стандарте SQL1 говорится, что при использоюнии предиката distinct в статистической функции ее аргументом должно быть простое имя столбца; аргумент не может быть выражением. Стандарт позволяет использовать предикат distinct в функциях sum () и avg О и не разрешает - в функциях min () и мах (), поскольку в этом нет смысла; тем не менее, в ряде СУБД подобное все-таки допустимо. Кроме того, стандарт требует применения предиката distinct в функции count {), но в ряде СУБД можно использовать функцию count () и без него. В функции count {*) данный предикат применять нельзя, поскольку она вообше не имеет отношения к столбцам, а просто подсчитывает число строк. В стандарте SQL2 упомянутые офаничения сняты и разрешается использовать предикат distinct во всех статистических функциях, а также применять выражения в качестве аргументов для всех функций.

Кроме того, предикат distinct в одном запросе можно употребить только один раз. Если он применяется вместе с аргументом одной из статистических функций, его нельзя использовать ни с одним другим аргументом. Если он указан перед списком возвращаемых столбцов, его нельзя употреблять ни в одной статистической функции. Единственным исключением из этого правила является случай, когда предикат distinct используется внутри подчиненного запроса, входящего в запрос, в котором предикат уже применяется. О подчиненных запросах рассказывается в главе 9.

Запросы с группировкой (предложение GROUP BY)

Результаты итоговых запросов, о которых до сих пор шла речь в настоящей главе, напоминают итоговую информацию, находящуюся обычно в конце отчета Эти запросы сжимают подробные данные, содержащиеся в отчете, в одну строку итоговых результатов. Но, как известно, в отчетах иногда используются также промежуточные итоги. И точно так же бьшает необходимо получать промежуточные итоги результатов запроса. Эту возможность предоставляет предложение group by

инструкции select.

Назначение предложения group by проще всего понять на примере. Рассмотрим два следующих запроса:

Какова средняя стоимость заказа?

SELECT AVG(AMOUNT) FROM ORDERS

AVG(AMOUNT)

$8,254.17

Какова средняя стоимость заказа для каждого служащего ?

SELECT REP, AVG(AMOUNT) FROM ORDERS GROUP BY REP

REP AVG (AMOUNT)

lOy ~ $3, 978.00

Первый запрос представляет собой простой итоговый запрос, аналогичный примерам, рассмотренным ранее. Второй запрос возвращает несколько итоговых строк - по одной строке для каждой группы. На рис. 8.3 изображена схема выполнения второго запроса. На логическом уровне запрос выполняется следующим образом:

1. Заказы делятся на группы, по одной группе для каждого служащего. В каждой группе все заказы имеют одно и то же значение в столбце rep.

2. Для каждой группы вычисляется среднее значение столбца amount по всем строкам, входящим в группу, и генерируется одна итоговая строка результатов Эта строка содержит значение столбца rep для группы и среднюю стоимость заказа для данной группы.

Таблица ORDERS

Таблица с группировкой

ШсЩл Выполнение запроса г /руппировкой

Результаты запроса

Запрос, включающий в себя предложение group by, называется запросом с группировкой, поскольку он объединяет строки исходных таблиц в группы и для каждой группы строк генерирует одну строку в таблице результатов запроса. Столбцы, указанные в предложении group by, называются столбцами группировки, поскольку именно они определяют, по какому признаку строки делятся на группы. Ниже приведен ряд запросов с группировкой:

Каков диапазон плановых объемов продаж для каждого офиса?