SQL - Урок 8. Группировка записей и функция COUNT()

А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией COUNT() . Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце.

В нашем примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic:

SELECT COUNT(id_topic) FROM posts;

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

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема - отдельная группа) и для каждой группы подсчитать количество строк:

Ну вот, в теме с id=1 у нас 3 сообщения, а с id=4 - одно. Кстати, если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL.

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

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;

В результате имеем:

В уроке 4 мы рассматривали, какие условия можно задавать оператором WHERE , те же условия можно задавать и оператором HAVING , только надо запомнить, что WHERE фильтрует строки, а HAVING - группы.

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

Чтобы определить количество записей в таблице MySQL, нужно воспользоваться специальной функцией COUNT().

Функция COUNT() возвращает количество записей в таблице, соответствующих заданному критерию.

Функция COUNT(expr) всегда считает только те строки, у которых результатом выражения expr является NOT NULL .

Исключением из этого правила является использование функции COUNT() со звездочкой в качестве аргумента - COUNT(*) . В этом случае считаются все строки, вне зависимости от того, NULL они или NOT NULL .

Например, функция COUNT(*) возвращает общее количество записей в таблице:

SELECT COUNT(*) FROM table_name

Как посчитать количество записей и вывести на экран

Пример PHP+MySQL-кода для подсчета и вывода общего количества строк:

$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $total = $row; // всего записей echo $total; ?>

Этот пример иллюстрирует самый простой вариант использования функции COUNT() . Но с помощью этой функции можно выплнять и другие задачи.

Указав определенный столбец таблицы в виде параметра, функция COUNT(column_name) возвращает количество записей этого столбца, которые не содержат значение NULL . Записи со значениями NULL игнорируются.

SELECT COUNT(column_name) FROM table_name

Использовать функцию mysql_num_rows() нельзя, потому что для того, чтобы узнать общее количество записей, нужно выполнить запрос SELECT * FROM db , то есть получить все записи, а это нежелательно, поэтому предпочтительнее использовать функцию count .

$result = mysql_query("SELECT COUNT (*) as rec FROM db");

Использование функции COUNT() на примере

Вот еще один пример использования функции COUNT() . Допустим, есть таблица ice_cream с каталогом мороженого, в которой находятся идентификаторы категорий и названия мороженого.

ВЫЧИСЛЕНИЯ

Итоговые функции

В выражениях SQL-запросов нередко требуется выполнить предварительную обработку данных. С этой целью используются специальные функции и выражения.

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

• COUNT (параметр ) — возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT. Например:

SELECT COUNT(*) FROM Клиенты;

SELECT COUNT(Сумма_заказа) FROM Клиенты;

SELECT COUNT(DISTINCT Сумма_заказа) FROM Клиенты;

Попытка выполнить следующий запрос приведет к сообщению об ошибке:

SELECT Регион , COUNT(*) FROM Клиенты ;

• SUM (параметр ) — возвращает сумму значений указанного в параметре столбца. Параметр может представлять собой и выражение, содержащее имя столбца. Например:

SELECT SUM (Сумма_заказа) FROM Клиенты;

Данное SQL-выражение возвращает таблицу, состоящую из одного столбца и одной записи и содержащую сумму всех определенных значений столбца Сумма_заказа из таблицы Клиенты.

Допустим, что в исходной таблице значения столбца Сумма_заказа выражены в рублях, а нам требуется вычислить общую сумму в долларах. Если текущий обменный курс равен, например, 27,8, то получить требуемый результат можно с помощью выражения:

SELECT SUM (Сумма_заказа*27.8) FROM Клиенты;

• AVG (параметр ) — возвращает среднее арифметическое всех значений указанного в параметре столбца. Параметр может представлять собой выражение, содержащее имя столбца. Например:

SELECT AVG (Сумма_заказа) FROM Клиенты;

SELECT AVG (Сумма_заказа*27.8) FROM Клиенты

WHERE Регион <> "Северо_3апад";

• МАХ (параметр ) — возвращает максимальное значение в столбце, указанном в параметре. Параметр может также представлять собой выражение, содержащее имя столбца. Например:

SELECT МАХ(Сумма__заказа) FROM Клиенты;

SELECT МАХ(Сумма_заказа*27.8) FROM Клиенты

W HERE Регион <> "Северо_3апад";

• MIN (параметр ) — возвращает минимальное значение в столбце, указанном в параметре. Параметр может представлять собой выражение, содержащее имя столбца. Например:

SELECT MIN(Сумма_заказа) FROM Клиенты;

SELECT MIN (Сумма__заказа*27 . 8) FROM Клиенты

W HERE Регион <> "Северо_3апад";

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

SELECT Регион, SUM (Сумма_заказа) FROM Клиенты

GROUP BY Регион;

Результатная таблица для данного запроса содержит имена регионов и итоговые (общие) суммы заказов всех клиентов из соответствующих регионов (рис. 5).

Теперь рассмотрим запрос на получение всех итоговых данных по регионам:

SELECT Регион, SUM (Сумма_заказа), AVG (Сумма_заказа), МАХ(Сумма_заказа), MIN (Сумма_заказа)

FROM Клиенты

GROUP BY Регион;

Исходная и результатная таблицы показаны на рис. 8. В примере только Северо-Западный регион представлен в исходной таблице более чем одной записью. Поэтому в результатной таблице для него различные итоговые функции дают различные значения.

Рис. 8. Итоговая таблица сумм заказов по регионам

При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако заголовки для значений итоговых функций и других столбцов вы можете задавать по своему усмотрению. Для этого достаточно после столбца в операторе SELECT указать выражение вида:

AS заголовок_столбца

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

SELECT Регион,

SUM (Сумма_заказа) AS [Общая сумма заказа],

AVG (Сумма_заказа) AS [Средняя сумма заказа],

МАХ(Сумма_заказа) AS Максимум,

MIN (Сумма_заказа) AS Минимум,

FROM Клиенты

GROUP BY Регион;

Рис. 9. Итоговая таблица сумм заказов по регионам с применением псевдонимов столбца

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

Итоговые функции можно использовать в выражениях SELECT и HAVING, но их нельзя применять в выражении WHERE. Oneратор HAVING аналогичен оператору WHERE, но в отличие от WHERE он отбирает записи в группах.

Допустим, требуется определить, в каких регионах более одного клиента. С этой целью можно воспользоваться таким запросом:

SELECT Регион , Count(*)

FROM Клиенты

GROUP BY Регион HAVING COUNT(*) > 1;

Функции обработки значений

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

• строковые функции;
• числовые функции;
• функции даты-времени.

Строковые функции

Строковые функции принимают в качестве параметра строку и возвращают после ее обработки строку или NULL.

• SUBSTRING (строка FROM начало ) — возвращает подстроку, получающуюся из строки, которая указана в качестве параметра строка . Подстрока начинается с символа, порядковый номер которого указан в параметре начало, и имеет длину, указанную в параметре длина. Нумерация символов строки ведется слева направо, начиная с 1. Квадратные скобки здесь указывают лишь на то, что заключенное в них выражение не является обязательным. Если выражение FOR длина не используется, то возвращается подстрока от начало и до конца исходной строки. Значения параметров начало и длина должны выбираться так, чтобы искомая подстрока действительно находилась внутри исходной строки. В противном случае функция SUBSTRING вернет NULL.

Например:

SUBSTRING ("Дорогая Маша!" FROM 9 FOR 4) — возвращает "Маша";

SUBSTRING ("Дорогая Маша! " FROM 9) —возвращает "Маша! ";

SUBSTRING("Дорогая Маша! " FROM 15) —возвращает NULL.

Использовать эту функцию в SQL-выражении можно, например, так:

SELECT * FROM Клиенты

WHERE SUBSTRING(Регион FROM 1 FOR 5) = "Север";

• UPPER (строка ) — переводит все символы указанной в параметре строки в верхний регистр.
• LOWER (строка ) — переводит все символы указанной в параметре строки в нижний регистр.
• TRIM (LEADING | TRAILING | BOTH ["символ"] FROM строка ) — удаляет ведущие (LEADING), заключительные (TRAILING) или те и другие (BOTH) символы из строки. По умолчанию удаляемым символом является пробел (" "), поэтому его можно не указывать. Чаще всего эта функция используется именно для удаления пробелов.

Например:

TRIM (LEADING " " FROM "город Санкт-Петербург") вращает " город Санкт-Петербург ";

TRIM(TRALING " " FROM "город Санкт-Петербург") возвращает "город Санкт-Петербург";

TRIM (BOTH " " FROM " город Санкт-Петербург ") — возвращает "город Санкт-Петербург";

TRIM(BOTH FROM " город Санкт-Петербург ") — возвращает "город Санкт-Петербург";

TRIM(BOTH "г" FROM "город Санкт-Петербург") — возвращает "ород Санкт-Петербур".

Среди этих функций наиболее часто используемые - SUBSTRING() И TRIM().

Числовые функции

Числовые функции в качестве параметра могут принимать данные не только числового типа, но возвращают всегда число или NULL (неопределенное значение).

• POSITION (целеваяСтрока IN строка ) — ищет вхождение целевой строки в указанную строку. В случае успешного поиска возвращает номер положения ее первого символа, иначе – 0. Если целевая строка имеет нулевую длину (например, строка " "), то функция возвращает 1. Если хотя бы один из параметров имеет значение NULL, то возвращается NULL. Нумерация символов строки ведется слева направо, начиная с 1.

Например:

POSITION ("e" IN "Привет всем") — возвращает 5;

POSITION ("всeм" IN "Привет всем") — возвращает 8;

POSITION (" " Привет всем") — возвращает 1;

POSITION("Привет!" IN "Привет всем") — возвращает 0.

В таблице Клиенты (см. рис. 1) столбец Адрес содержит, кроме названия города, почтовый индекс, название улицы и другие данные. Возможно, вам потребуется выбрать записи о клиентах, проживающих в определенном городе. Так, если требуется выбрать записи, относящиеся к клиентам, проживающим в Санкт-Петербурге, то можно воспользоваться следующим выражением SQL-запроса:

SELECT * FROM Клиенты

WHERE POSITION (" Санкт - Петербург " IN Адрес ) > 0;

Заметим, что этот простой запрос на выборку данных можно сформулировать иначе:

SELECT * FROM Клиенты

WHERE Адрес LIKE " %Петербург% ";

• EXTRACT (параметр ) — извлекает элемент из значения типа дата-время или из интервала. Например:

EXTRACT (MONTH FROM DATE "2005-10-25") — возвращает 10.

• CHARACTER_LENGTH (строка ) — возвращает количество символов в строке.

Например:

CHARACTER_LENGTH("Привет всем") — возвращает 11.

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

ABS (-123) —возвращает 123;

ABS (2 - 5) — возвращает 3.

• МО D (число1, число2 ) — возвращает остаток от целочисленного деления первого числа на второе. Например:

MOD (5, з) — возвращает 2;

MOD (2, з) — возвращает 0.

• LN (число ) — возвращает натуральный логарифм числа.
• ЕХР (число ) — возвращает е число (основание натурального логарифма в степени число).
• POWER (число1, число2 ) — возвращает число1 число2 (число1 в степени число2).
• SQRT (число ) — возвращает квадратный корень из числа.
• FLOOR (число ) — возвращает наибольшее целое число, не превышающее заданное параметром (округление в меньшую сторону). Например:

FLOOR (5.123) — возвращает 5.0.

• CEIL (число ) или CEILING (число ) — возвращает наименьшее целое число, которое не меньше заданного параметром округление в большую сторону). Например:

CEIL (5.123) — возвращает 6. 0.

• WIDTH_BUCKET (число1, число2, числоЗ, число4) возвращает целое число в диапазоне между 0 и число4 + 1. Параметры число2 и числоЗ задают числовой отрезок, разделенный на равновеликие интервалы, количество которых задается параметром число 4. Функция определяет номер интервала, в который попадает значение число1. Если число1 находится за пределами заданного диапазона, то функция возвращает 0 или число 4 + 1. Например:

WIDTH_BUCKET(3.14, 0, 9, 5) — возвращает 2.

Функции даты-времени

В языке SQL имеются три функции, которые возвращают текущие дату и время.

• CURRENT_DATE — возвращает текущую дату (тип DATE).

Например: 2005-06-18.

• CURRENT_TIME (число ) — возвращает текущее время (тип TIME). Целочисленный параметр указывает точность представления секунд. Например, при значении 2 секунды будут представлены с точностью до сотых (две цифры в дробной части):

12:39:45.27.

• CURRENT_TIMESTAMP (число ) — возвращает дату и время (тип TIMESTAMP). Например, 2005-06-18 12:39:45.27. Целочисленный параметр указывает точность представления секунд.

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

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

Вычисляемые выражения

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

Логические операторы AND, OR и NOT и функции были рассмотрены ранее.

Арифметические операторы:

• + — сложение;
• - — вычитание;
• * — умножение;
• / — деление.

Строковый оператор только один — оператор конкатенации или склейки строк (| |). В некоторых реализациях SQL (например, Microsoft Access) вместо (| |) используется символ (+). Оператор конкатенации приписывает вторую строку к концу первой пример, выражение:

"Саша" | | "любит" | | " Машу"

вернет в качестве результата строку " Сашалюбит Машу".

При составлении выражений необходимо следить, чтобы операнды операторов имели допустимые типы. Например, выражение: 123 + "Саша" недопустимо, поскольку арифметический оператор сложения применяется к строковому операнду.

Вычисляемые выражения могут находиться после оператора SELECT, а также в выражениях условий операторов WHERE и HAVI NG .

Рассмотрим несколько примеров.

Пусть таблица Продажи содержит столбцы Тип_товара, Количество и Цена, а нам требуется знать выручку для каждого типа товара. Для этого достаточно в список столбцов после оператора SELECT включить выражение Количество*Цена:

SELECT Тип_товара, Количество, Цена, Количество*Цена AS

Итого FROM Продажи;

Здесь используется ключевое слово AS (как) для задания псевдонима столбца с вычисляемыми данными.

На рис. 10 показаны исходная таблица Продажи и результатная таблица запроса.

Рис. 10. Результат запроса с вычислением выручки по каждому типу товара

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

SELECT SUM (Количество*Цена) FROM Продажи;

Следующий запрос содержит вычисляемые выражения и в списке столбцов, и в условии оператора WHERE. Он выбирает из таблицы продажи те товары, выручка от продажи которых больше 1000:

SELECT Тип_товара, Количество*Цена AS Итого

FROM Продажи

WHERE Количество*Цена > 1000;

Предположим, что требуется получить таблицу, в которой два столбца:

Товар, содержащий тип товара и цену;

Итого, содержащий выручку.

Поскольку предполагается, что в исходной таблице продажи столбец Тип_товара является символьным (тип CHAR), а столбец Цена — числовой, то при объединении (склейке) данных из этих столбцов необходимо выполнить приведение числового типа к символьному с помощью функции CAST (). Запрос, выполняющий это задание, выглядит так (рис. 11):

SELECT Тип_товара | | " (Цена: " | | CAST(Цена AS CHAR(5)) | | ")" AS Товар, Количество*Цена AS Итого

FROM Продажи;

Рис. 11. Результат запроса с объединением разнотипных данных в одном столбце

Примечание. В Microsoft Access аналогичный запрос будет иметь следующий вид:

SELECT Тип_товара + " (Цена: " + C Str (Цена) + ")" AS Товар,

Количество*Цена AS Итого

FROM Продажи;

Условные выражения с оператором CASE

В обычных языках программирования имеются операторы условного перехода, которые позволяют управлять вычислительным процессом в зависимости от того, выполняется или нет некоторое условие. В языке SQL таким оператором является CASE (случай, обстоятельство, экземпляр ). В SQL:2003 этот оператор возвращает значение и, следовательно, может использоваться в выражениях. Он имеет две основные формы, которые мы рассмотрим в данном разделе.

Оператор CASE со значениями

Оператор CASE со значениями имеет следующий синтаксис:

CASE проверяемое_значение

WHEN значение1 THEN результат1

WHEN значение2 THEN резулътат2

. . .

WHEN значением N THEN результат N

ELSE результатХ

В случае, когда проверяемое_значение равно значение1 , оператор CASE возвращает значение результат1 , указанное после ключевого слова THEN (то). В противном случае проверяемое_значение сравнивается с значение2 , и если они равны, то возвращается значение результат2. В противном случае проверяемое значение сравнивается со следующим значением, указанным после ключевого слова WHEN (когда) и т. д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результат X , указанное после ключевого слова ELSE (иначе).

Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.

Допустим, на основе таблицы Клиенты (см. рис. 1) требуется получить таблицу, в которой названия регионов заменены их кодовыми номерами. Если в исходной таблице различных регионов не слишком много, то для решения данной задачи удобно воспользоваться запросом с оператором CASE:

SELECT Имя , Адрес ,

CASE Регион

WHEN " Москва " THEN "77"

WHEN "Тверская область" THEN "69"

. . .

ELSE Регион

AS Код региона

FROM Клиенты;

Оператор CASE с условиями поиска

Вторая форма оператора CASE предполагает его использование при поиске в таблице тех записей, которые удовлетворяют определенному условию:

CASE

WHEN условие1 THEN результат1

WHEN уоловие2 THEN результат2

. . .

WHEN условие N THEN результат N

ELSE результатХ

Оператор CASE проверяет, истинно ли условие1 для первой записи в наборе, определенном оператором WHERE, или во всей таблице, если WHERE отсутствует. Если да, то CASE возвращает значение результат1. В противном случае для данной записи проверяется условие2. Если оно истинно, то возвращается значение результат2 и т. д. Если ни одно из условий не выполняется, то возвращается значение результат X , указанное после ключ го слова ELSE.

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

Предположим, в таблице книги (Название, Цена) столбец имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":

SELECT Название,

CASE

WHEN Цена IS NULL THEN " Нет в наличии "

ELSE CAST(Цена AS CHAR(8))

AS Цена

FROM Книги;

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

Обратите внимание, что вместо первой формы оператора CASE всегда можно использовать вторую:

CASE

WHEN проверяемое_значение = значение1 THEN результат1

WHEN проверяемое_значение = значение2 THEN результат2

. . .

WHEN проверяемое_значение = значение N THEN peзyльтaтN

ELSE резулътатХ

Функции NULLIF и COALESCE

В ряде случаев, особенно в запросах на обновление данных (оператор UPDATE), удобно использовать вместо громоздкого оператора CASE более компактные функции NULLIF () (NULL, если) и COALESCE() (объединять).

Функция NULLIF (значение1, значение2 ) возвращает NULL, если значение первого параметра соответствует значению второго параметра, в случае несоответствия возвращается значение первого параметра без изменений. То есть если равенство значение1 = значение2 выполняется, то функция возвращает NULL, иначе — значение значение1.

Данная функция эквивалентна оператору CASE в следующих двух формах:

• CASE значение1

WHEN значение2 THEN NULL

ELSE значение1

• CASE

WHEN значение1 = значение2 THEN NULL

ELSE значение1

Функция COALESCE(значение1, значение2, ... , значение N ) принимает список значений, которые могут быть как определенными, так и неопределенными (NULL). Функция возвращает определенное значение из списка или NULL, если все значения не определены.

Данная функция эквивалентна следующему оператору CASE:

CASE

WHEN значение 1 IS NOT NULL THEN значение 1

WHEN значение 2 IS NOT NULL THEN значение 2

. . .

WHEN значение N IS NOT NULL THEN значение N

ELSE NULL

Предположим, что в таблице Книги (Название, Цена) столбец Цена имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":

SELECT Название, COALESCE (CAST(Цена AS CHAR(8)),

"Нет в наличии") AS Цена

FROM Книги;

Будем учиться подводить итоги. Нет, это ещё не итоги изучения 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 - о денежных суммах, вырученных за подачу объявлений.

Используя оператор 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