Порахувати кількість id із певним значенням. Вибірка та підрахунок рядків одним запитом – SQL_CALC_FOUND_ROWS. Побудова обчислюваних полів

У цьому навчальному посібнику ви дізнаєтесь, як використовувати функцію COUNTв SQL Server (Transact-SQL) з синтаксисом та прикладами.

Опис

У SQL Server (Transact-SQL) функція COUNTповертає кількість рядків поля чи виразу в результуючий набір.

Синтаксис

Синтаксис функції COUNT у SQL Server (Transact-SQL):

АБО синтаксис функції COUNT при групуванні результатів одного або кількох стовпців:

Параметри чи аргументи

expression1 , expression2 , … expression_n
Вирази, які не укладені у функцію COUNT та мають бути включені до пропозиції GROUP BY наприкінці SQL пропозиції.
aggregate_expression - це стовпець або вираз, чиї NULL значення не будуть підраховані.
tables — таблиці, з яких потрібно отримати записи. Має бути хоча б одна таблиця, перерахована в операторі FROM.
WHERE conditions – необов'язковий. Це умови, які потрібно виконувати для вибраних записів.

Увімкнення не NULL значень

Не всі це розуміють, але функція COUNT вважатиме ті записи, де значення висловлювання в COUNT (aggregate_expression ) не дорівнює NULL. Коли вираз містить значення NULL, воно не включається до лічильника COUNT.

Розглянемо приклад функції COUNT, який показує, як значення NULL оцінюються функцією COUNT.

Наприклад, якщо у вас є наступна таблиця, яка називається markets :

Цей приклад COUNT поверне 3, тому що всі значення market_id у наборі результатів запиту НЕ NULL.

Однак, якщо ви запустили наступний оператор SELECT, який використовує функцію COUNT:

Transact-SQL

SELECT COUNT(filials) FROM markets; --Результат: 1

Цей приклад COUNT буде повертати лише 1, оскільки тільки одне значення filials у наборі результатів запиту НЕ NULL. Це буде перший рядок, де буде вказано filials=yes. Це єдиний рядок, який включено до обчислення функції COUNT.

Застосування

Функція COUNT може використовуватись у наступних версіях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Приклад із одним полем

Розглянемо деякі приклади SQL Server функції COUNT, щоб зрозуміти, як використовувати функцію COUNT у SQL Server (Transact-SQL).

Наприклад, ви можете дізнатися, скільки contacts має користувач з last_name = Rasputin.

У цьому прикладі функції COUNT ми виразу COUNT (*) вказали аліас Number of contacts. Тому в результуючому наборі відображатиметься «Number of contacts» як ім'я поля.

Приклад із використанням DISTINCT

Ви можете використовувати оператор DISTINCT у функції COUNT. Наприклад, наведений нижче оператор SQL повертає кількість унікальних department , де хоча один співробітник має first_name = 'Samvel'.

Щоб визначити кількість записів у таблиці 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(*) як 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 ("всім" 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 результат 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 Книги;

Описується використання арифметичних операторів та побудова обчислюваних стовпців. Розглядаються підсумкові (агрегатні) функції COUNT, SUM, AVG, MAX, MIN. Надається приклад використання оператора GROUP BY для групування у запитах вибірки даних. Описується застосування пропозиції HAVING.

Побудова обчислюваних полів

У загальному випадку для створення обчислюваного (похідного) поляу списку SELECT слід вказати деякий вираз мови SQL. У цих виразах застосовуються арифметичні операції додавання, віднімання, множення та поділу, а також вбудовані функції мови SQL. Можна вказати ім'я будь-якого стовпця (поля) таблиці чи запиту, але використовувати ім'я стовпця лише тієї таблиці чи запиту, які вказані у списку пропозиції FROM відповідної інструкції. При побудові складних виразів можуть знадобитися дужки.

Стандарти SQL дозволяють явно задавати імена стовпців результуючої таблиці, для чого застосовується фраза AS .

SELECT Товар.Назва, Товар.Ціна, Угода.Кількість, Товар.Ціна*Угода.Кількість AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара Приклад 6.1. Розрахунок загальної вартості кожної угоди.

Приклад 6.2.Отримати список фірм із зазначенням прізвища та ініціалів клієнтів.

SELECT Фірма, Прізвище+""+ Left(Ім'я,1)+"."+Left(По батькові,1)+"."AS ПІБ FROM Клієнт Приклад 6.2. Отримання списку фірм із зазначенням прізвища та ініціалів клієнтів.

У запиті використано вбудовану функцію Left , що дозволяє вирізати в текстовій змінній один символ зліва в даному випадку.

Приклад 6.3.Отримати список товарів із зазначенням року та місяця продажу.

SELECT Товар.Назва, Year(Угода.Дата) AS Год, Month(Угода.Дата) AS Місяць FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара Приклад 6.3. Отримання списку товарів із зазначенням року та місяця продажу.

У запиті використані вбудовані функції Year та Month для виділення року та місяця з дати.

Використання підсумкових функцій

За допомогою підсумкових (агрегатних) функційв рамках SQL-запиту можна отримати ряд узагальнюючих статистичних відомостей про безліч відібраних значень вихідного набору.

Користувачеві доступні такі основні підсумкові функції:

• Count (Вираз) - визначає кількість записів у вихідному наборі SQL-запиту;
• Min/Max (Вираз) - визначають найменше та найбільше з безлічі значень у деякому полі запиту;
• Avg (Вираз) - ця функція дозволяє розрахувати середнє значення безлічі значень, що зберігаються у певному полі відібраних запитом записів. Воно є арифметичним середнім значенням, тобто. сумою значень, поділеної з їхньої кількість.
• Sum (Вираз) – обчислює суму безлічі значень, що містяться в певному полі відібраних запитом записів.

Найчастіше як вираз виступають імена стовпців. Вираз може обчислюватися і за значеннями кількох таблиць.

Всі ці функції оперують зі значеннями в єдиному стовпчику таблиці або з арифметичним виразом та повертають єдине значення. Функції COUNT , MIN та MAX застосовні як до числових, так і до нечислових полів, тоді як функції SUM та AVG можуть використовуватися лише у разі числових полів, за винятком COUNT(*) . При обчисленні результатів будь-яких функцій спочатку виключаються всі порожні значення, після чого необхідна операція застосовується тільки до конкретних значень стовпця, що залишилися. Варіант COUNT(*) - особливий випадок використання функції COUNT, його призначення полягає у підрахунку всіх рядків у результуючій таблиці, незалежно від того, містяться там порожні, дублюючі або будь-які інші значення.

Якщо перед застосуванням узагальнюючої функції необхідно виключити значення, що дублюються, слід перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT . Воно не має сенсу для функцій MIN і MAX, проте його використання може вплинути на результати виконання функцій SUM і AVG, тому необхідно заздалегідь обміркувати, чи воно має бути присутнім у кожному конкретному випадку. Крім того, ключове слово DISTINCT може бути вказане у будь-якому запиті не більше одного разу.

Дуже важливо відзначити, що підсумкові функціїможуть використовуватися лише у списку пропозиції SELECT та у складі пропозиції HAVING. В інших випадках це неприпустимо. Якщо список у пропозиції SELECT містить підсумкові функції, а в тексті запиту відсутня фраза GROUP BY , що забезпечує об'єднання даних у групи, то жоден із елементів списку пропозиції SELECT не може включати будь-яких посилань на поля, за винятком ситуації, коли поля виступають як аргументи підсумкових функцій.

Приклад 6.4.Визначити першу за алфавітом назву товару.

SELECT Min(Товарна назва) AS Min_Назва FROM Товар Приклад 6.4. Визначення першої за алфавітом назви товару.

Приклад 6.5.Визначити кількість угод.

SELECT Count(*) AS Кількість угод FROM Угода Приклад 6.5. Визначити кількість угод.

Приклад 6.6.Визначити сумарну кількість проданого товару.

SELECT Sum(Угода.Кількість) AS Кількість_товару FROM Угода Приклад 6.6. Визначення сумарної кількості проданого товару.

Приклад 6.7.Визначити середню ціну проданого товару.

SELECT Avg(Товар.Ціна) AS Avg_Ціна FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара; Приклад 6.7. Визначення середньої ціни проданого товару.

SELECT Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара Приклад 6.8. Підрахунок вартості проданих товарів.

Пропозиція GROUP BY

Часто у запитах потрібно формувати проміжні підсумки, що зазвичай відображається появою запиту фрази " кожного ... " . Для цього в операторі SELECT використовується пропозиція GROUP BY . Запит, в якому присутній GROUP BY , називається запитом, що групує, оскільки в ньому групуються дані, отримані в результаті виконання операції SELECT , після чого для кожної окремої групи створюється єдиний сумарний рядок. Стандарт SQL вимагає, щоб пропозиція SELECT та фраза GROUP BY були тісно пов'язані між собою. За наявності в операторі SELECT фрази GROUP BY кожен елемент списку у реченні SELECT повинен мати єдине значення для всієї групи. Більше того, пропозиція SELECT може включати лише такі типи елементів: імена полів, підсумкові функції, константи та вирази, що включають комбінації перерахованих вище елементів.

Всі імена полів, наведені в списку пропозиції SELECT, повинні бути присутніми і у фразі GROUP BY - за винятком випадків, коли ім'я стовпця використовується в підсумкової функції. Зворотне правило не є справедливим – у фразі GROUP BY можуть бути імена стовпців, які відсутні у списку пропозиції SELECT.

Якщо спільно з GROUP BY використовується пропозиція WHERE , вона обробляється першим, а групуванню піддаються ті рядки, які задовольняють умові пошуку.

Стандартом SQL визначено, що з проведенні групування всі відсутні значення розглядаються як рівні. Якщо два рядки таблиці в тому самому групованому стовпці містять значення NULL і ідентичні значення у всіх інших непустих групованих стовпцях, вони поміщаються в одну і ту ж групу.

Приклад 6.9.Обчислити середній обсяг покупок, здійснених кожним покупцем.

SELECT Клієнт.Прізвище, Avg(Угода.Кількість) AS Середня_кількість FROM Клієнт INNER JOIN Угода ON Клієнт.КодКлієнта=Угода.КодКлієнта GROUP BY Клієнт.Прізвище Приклад 6.9. Обчислення середнього обсягу покупок, здійснених кожним покупцем.

Фраза "кожним покупцем" знайшла своє відображення у SQL-запиті у вигляді пропозиції GROUP BY Клієнт.Прізвище.

Приклад 6.10.Визначити, яку суму було продано товар кожного найменування.

SELECT Товар.Назва, Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара GROUP BY Товар.Назва Приклад 6.10. Визначення, яку суму було продано товар кожного найменування.

SELECT Клієнт.Фірма, Count(Угода.КодУгоди) AS Кількість угод FROM Клієнт INNER JOIN Угода ON Клієнт.КодКлієнта=Угода.КодКлієнта GROUP BY Клієнт.Фірма Приклад 6.11. Підрахунок кількості угод, здійснених кожною фірмою.

SELECT Клієнт.Фірма, Sum(Угода.Кількість) AS Загальна_Кількість, Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN (Клієнт INNER JOIN Угода ON Клієнт.КодКлієнта=Угода.КодКлієнта) .КодТовара GROUP BY Клієнт.Фірма Приклад 6.12. Підрахунок загальної кількості купленого кожної фірми товару та її вартості.

Приклад 6.13.Визначити сумарну вартість кожного товару за кожний місяць.

SELECT Товар.Назва, Month(Угода.Дата) AS Місяць, Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара GROUP BY Товар.Назва, Month(Угода.Дата) ) Приклад 6.13. Визначення сумарної вартості кожного товару за кожний місяць.

Приклад 6.14.Визначити сумарну вартість кожного товару першого ґатунку за кожен місяць.

SELECT Товар.Назва, Month(Угода.Дата) AS Місяць, Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара WHERE Товар.Сорт="Перший" GROUP BY Товар. .Назва, Month(Угода.Дата) Приклад 6.14. Визначення сумарної вартості кожного товару першого ґатунку за кожен місяць.

Пропозиція HAVING

За допомогою HAVING відображаються всі заздалегідь згруповані за допомогою GROUP BY блоки даних, що задовольняють заданим HAVING умовам. Це додаткова можливість профільтрувати вихідний набір.

Умови HAVING відрізняються від умов WHERE :

• HAVING виключає із результуючого набору даних групи з результатами агрегованих значень;
• WHERE виключає з розрахунку агрегатних значень щодо угруповання запису, що не задовольняють умові;
• за умови пошуку WHERE не можна задавати агрегатні функції.

Приклад 6.15.Визначити фірми, у яких загальна кількість угод перевищила три.

SELECT Клієнт.Фірма, Count(Угода.Кількість) AS Кількість_угод FROM Клієнт INNER JOIN Угода ON Клієнт.КодКлієнта=Угода.КодКлієнта GROUP BY Клієнт.Фірма HAVING Count(Угода.Кількість)>3 Приклад 6.15. Визначення фірм, у яких загальна кількість угод перевищила три.

Приклад 6.16.Вивести список товарів, проданих у сумі понад 10000 крб.

SELECT Товар.Назва, Sum(Товар.Ціна*Угода.Кількість) AS Вартість FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара GROUP BY Товар.Назва HAVING Sum(Товар.Ціна*Угода.Кількість)>1000 Приклад 6.16. Висновок списку товарів, проданих у сумі понад 10000 крб.

Приклад 6.17.Вивести список товарів, проданих у сумі понад 10000 без зазначення суми.

SELECT Товар.Назва FROM Товар INNER JOIN Угода ON Товар.КодТовара=Угода.КодТовара GROUP BY Товар.Назва HAVING Sum(Товар.Ціна*Угода.Кількість)>10000 Приклад 6.17. Виведення списку товарів, проданих у сумі понад 10000 без зазначення суми.