Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

SELECT SUM (ИМЯ_СТОЛБЦА) ...

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос:

SELECT SUM (Salary) FROM Staff

Этот запрос вернёт значение 287664,63.

А теперь . В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица - те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения . В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения .

Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц) .

Функция SQL AVG

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица - те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Результатом будет значение 6,33

Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк) таблицы.

Пример 9. База данных и таблица - те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос:

SELECT COUNT (Comm) FROM Staff

Результатом будет значение 11.

Пример 10. База данных и таблица - те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:

SELECT COUNT (*) FROM Staff

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.

Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору .

Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.

Category	Part	Units	Money
Транспорт	Автомашины	110	17600
Недвижимость	Квартиры	89	18690
Недвижимость	Дачи	57	11970
Транспорт	Мотоциклы	131	20960
Стройматериалы	Доски	68	7140
Электротехника	Телевизоры	127	8255
Электротехника	Холодильники	137	8905
Стройматериалы	Регипс	112	11760
Досуг	Книги	96	6240
Недвижимость	Дома	47	9870
Досуг	Музыка	117	7605
Досуг	Игры	41	2665

Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос:

SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category

Пример 13. База данных и таблица - та же, что в предыдущем примере.

Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос:

SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category

Результатом будет следующая таблица:

Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION .

Реляционные базы данных и язык SQL

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

Шаг 15. Функции SUM, AVG, MIN, MAX, COUNT…

На этом шаге будут показаны варианты использования простейших функций в SQL, таких как сумма, минимальное и максимальное значения, среднее значение и т.п. Сразу же начнем с примера вывода среднего значения стажа всех сотрудников.

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНИЙ СТАЖ СОТРУДНИКОВ] FROM D_STAFF

SQL функция AVG.

Аналогично можно вычислить минимальное и максимальное значения (MIN, MAX), общую сумму (SUM) и т.д. Советую попробовать это выполнить с использованием обучающей программы. Стоит попробовать определить дополнительные критерии отбора записей, участвующих в определении итогового значения функции с использованием оператора WHERE.

Перечисленные выше функции для определения своего значения используют результат запроса целиком. Такие функции называются агрегатными . Также, есть ряд функций, аргументом которых являются не все значения определенной в запросе колонки, а каждое отдельное значение каждой отдельной строки результата. Примером такой функции является SQL функция вычисления длины текстового поля LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [ДЛИНА] FROM D_STAFF

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

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

SQL функция MAX.

Ну и в заключении все вместе.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [СУММА], AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНЕЕ], MIN(D_STAFF.S_EXPERIENCE) AS [МИНИМУМ], MAX(D_STAFF.S_EXPERIENCE) AS [МАКСИМУМ], COUNT(*) AS [КОЛИЧЕСТВО ЗАПИСЕЙ], MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

Пример использования агрегатных SQL функций.

Обратите внимание на аргумент функции COUNT. Я указал в качестве аргумента (*), поскольку хочу получиться именно общее число записей. Если указать, например COUNT(S_NAME), то результатом будет число непустых значений S_NAME (S_NAME IS NOT NULL). Можно было бы написать COUNT(DISTINCT S_NAME) и получить количество уникальных значений S_NAME, но MS Access такой вариант, к сожалению, не поддерживает. В нашем примере COUNT(S_NAME) и COUNT(*) дают абсолютно одинаковый результат.

Шаг 16. Преобразование текста

Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE и LCASE. Пример запроса и результат его обработки приведены ниже:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL функции UCASE и LCASE.

Шаг 17. SQL и работа со строками

Есть еще такая замечательная функция MID, которая поможет вам решить задачу выделения части строки из всего значения текстового поля. Здесь также лучшим комментарием будет пример – пример "издевательств" над наименованиями профилей пользователей.

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Суперпозиция SQL функций UCASE и MID.

Мы “вырезали” из значений наименований профилей по 5 символов, начиная с 3-го, и получили кучу повторяющегося “мусора”. Для того чтобы оставить только уникальные значения будем использовать ключевое слово DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Выбор уникальных значений агрегатной функции.

Иногда приходится в качестве аргументов функции MID использовать выражения с функцией LEN. В следующем примере мы уже выводим последние 5 символов в наименованиях профилей.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Использование SQL функции LEN.

Шаг 18. Использование SQL функций в критерии отбора записей. Оператор HAVING

Разобравшись с функциями, практически сразу возникает вопрос, как их можно использовать в критериях отбора записей? Некоторые функции, а именно те, которые не являются агрегатными - использовать достаточно легко. Вот, например, список сотрудников, чье Ф.И.О. более 25 символов.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Использование неагрегатной функции LEN в условиях SQL запроса.

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

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Для таких случаев в SQL ввели ключевое слово HAVING, которое поможет нам решить проблему с должностями и сотрудниками.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

Использование агрегатных функций в условиях SQL запроса.

Шаг 19. Группировка данных в результатах SQL запроса оператором GROUP BY

Оператор GROUP BY необходим для группировки значений агрегатных функций по значениям связанных с ними полей. Он необходим, когда мы хотим использовать значение агрегатной функции в критерии отбора записей (предыдущий шаг). Он также необходим, когда мы хотим включить в результат запроса значение агрегатной функции. Но в самом простом варианте группировка эквивалента выделению уникальных значений колонки. Смотрим пример запроса.

SELECT S_POSITION FROM D_STAFF

А это два варианта, позволяющие вывести только уникальные значения S_POSITION.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

Ну а теперь вернемся к группировке значений функций по значениям связанных с ними полей. Выведем для каждого профиля пользователя количество ассоциированных с ним записей в таблице D_STAFF_PROFILE.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

Использование агрегатной SQL функции вместе с группировкой.

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

SELECT S.S_POSITION AS , S.S_NAME AS [СОТРУДНИК], COUNT(SP.STAFF_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ В ТАБЛИЦЕ D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S.S_NAME

Группировка строк результата SQL запроса по нескольким полям.

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.

Пример. Найти минимальную и максимальную цену на персональные компьютеры:

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

Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):

Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).

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

Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .

Предложение GROUP BY

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model;

В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:

model	Qty_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).

Существует несколько определенных правил выполнения агрегатных функций :

• Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
• Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
• Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
• Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .

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

Предложение HAVING

Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .