ОБЧИСЛЕННЯ

підсумкові функції

У виразах 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 (*)\u003e 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) \u003d "Північ";

• 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 Адреса)\u003e 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 і HAVING.

Розглянемо кілька прикладів.

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

SELECT Тіп_товара, Кількість, Ціна, Кількість * ЦінаAS

Разом FROM Продажі;

Тут використовується ключове слово AS (як) для завдання псевдоніма стовпчика з обчислюються даними.

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

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

Якщо потрібно дізнатися загальну виручку від продажу всіх товарів, то достатньо застосувати наступний запит:

SELECT SUM (Кількість * Ціна) FROM Продажі;

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

SELECT Тіп_товара, Кількість * Ціна AS Разом

FROM Продажі

WHERE Кількість * Ціна\u003e 1000;

Припустимо, що потрібно отримати таблицю, в якій два стовпці:

Товар, який містить тип товару і ціну;

Разом, що містить виручку.

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

SELECT Тіп_товара | | "(Ціна:" | | CAST (Ціна AS CHAR (5)) | | ")" AS Товар, Кількість * Ціна AS Разом

FROM Продажі;

Мал. 11. Результат запиту з об'єднанням різнотипних даних в одному стовпці

Примітка. У Microsoft Access аналогічний запит буде мати наступний вигляд:

SELECT Тіп_товара + "(Ціна:" + CStr (Ціна) + ")" 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 проверяемое_значеніе \u003d значення1 THEN результат1

WHEN проверяемое_значеніе \u003d значення2 THEN результат2

. . .

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

ELSE резул'татХ

Функції NULLIF і COALESCE

У ряді випадків, особливо в запитах на оновлення даних (оператор UPDATE), зручно використовувати замість громіздкого оператора CASE більш компактні функції NULLIF () (NULL, якщо) і COALESCE () (об'єднувати).

Функція NULLIF ( значення1, значення2) Повертає NULL, якщо значення першого параметра відповідає значенню другого параметра, в разі невідповідності повертається значення першого параметра без змін. Тобто якщо рівність значення1 \u003d значення2 виконується, то функція повертає NULL, інакше значення значення1.

Ця функція еквівалентна оператору CASE в наступних двох формах:

• CASE значення1

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

ELSE значення1

• CASE

WHEN значення1 \u003d значення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 - Урок 11. Підсумкові функції, обчислювані стовпці та подання

Підсумкові функції ще називають статистичними, агрегатними або підсумовують. Ці функції обробляють набір рядків для підрахунку і повернення одного значення. Таких функцій всього п'ять:

• AVG () Функція повертає середнє значення стовпця.


• COUNT () Функція повертає число рядків в стовпці.


• MAX () Функція повертає найбільше значення в стовпці.


• MIN () Функція повертає найменше значення в стовпці.


• SUM () Функція повертає суму значень стовпця.

З одного з них - COUNT () - ми вже познайомилися в уроці 8 . Зараз познайомимося з іншими. Припустимо, ми захотіли дізнатися мінімальну, максимальну і середню ціну на книги в нашому магазині. Тоді з таблиці Ціни (prices) треба взяти мінімальне, максимальне і середнє значення по стовпцю price. Запит простий:

SELECT MIN (price), MAX (price), AVG (price) FROM prices;

Тепер, ми хочемо дізнатися, на яку суму нам привіз товар постачальник "Будинок друку" (id \u003d 2). Скласти такий запит не так просто. Давайте поміркуємо, як його скласти:

1. Спочатку треба з таблиці Поставки (incoming) вибрати ідентифікатори (id_incoming) тих поставок, які здійснювалися постачальником "Будинок друку" (id \u003d 2):

2. Тепер з таблиці Журнал поставок (magazine_incoming) треба вибрати товари (id_product) і їх кількості (quantity), які здійснювалися в знайдених в пункті 1 поставках. Тобто запит з пункту 1 стає вкладеним:

3. Тепер нам треба додати в результуючу таблицю ціни на знайдені товари, які зберігаються в таблиці Ціни (prices). Тобто нам знадобиться об'єднання таблиць Журнал поставок (magazine_incoming) і Ціни (prices) за стовпцем id_product:

4. У вийшла таблиці явно не вистачає стовпця Сума, тобто обчислюється стовпчика. Можливість створення таких стовпців передбачена в MySQL. Для цього треба лише вказати в запиті ім'я обчислюваного стовпця і що він повинен обчислювати. У нашому прикладі такий стовпець буде називатися summa, а обчислювати він буде твір стовпців quantity і price. Назва нового стовпця відділяється словом AS:

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product \u003d prices.id_product AND id_incoming \u003d (SELECT id_incoming FROM incoming WHERE id_vendor \u003d 2);

5. Дуже добре, нам залишилося лише підсумувати стовпець summa і нарешті дізнаємося, на яку суму нам привіз товар постачальник "Будинок друку". Синтаксис для використання функції SUM () следущий:

SELECT SUM (ім'я_стовпця) FROM ім'я_таблиці;

Ім'я стовпця нам відомо - summa, а ось імені таблиці у нас немає, так як вона є результатом запиту. Що ж робити? Для таких випадків в MySQL існують Уявлення. Уявлення - це запит на вибірку, якому присвоюється унікальне ім'я і який можна зберігати в базі даних, для подальшого використання.

Синтаксис створення уявлення наступний:

CREATE VIEW імя_представленія AS запит;

Давайте збережемо наш запит, як уявлення з ім'ям report_vendor:

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product \u003d prices.id_product AND id_incoming \u003d (SELECT id_incoming FROM incoming WHERE id_vendor \u003d 2 );

6. Ось тепер можна використовувати підсумкову функцію SUM ():

SELECT SUM (summa) FROM report_vendor;

Ось ми і досягли результату, правда для цього нам довелося використовувати вкладені запити, об'єднання, обчислювані стовпці та подання. Так, іноді для отримання результату доводиться подумати, без цього нікуди. Зате ми торкнулися двох дуже важливих тем - обчислювані стовпці та подання. Давайте поговоримо про них детальніше.

Обчислювані поля (стовпці)

На прикладі ми розглянули сьогодні математичне обчислюване поле. Тут хотілося б додати, що використовувати можна не тільки операцію множення (*), але і віднімання (-), і додавання (+), і ділення (/). Синтаксис наступний:

SELECT імя_столбца_1, імя_столбца_2, імя_столбца_1 * імя_столбца_2 AS імя_вичісляемого_столбца FROM ім'я_таблиці;

Другий нюанс - ключове слово AS, ми його використовували для завдання імені обчислюється стовпчика. Насправді за допомогою цього ключового слова задаються псевдоніми для будь-яких стовпців. Навіщо це потрібно? Для скорочення і читання коду. Наприклад, наше уявлення могло б виглядати так:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product \u003d B.id_product AND id_incoming \u003d (SELECT id_incoming FROM incoming WHERE id_vendor \u003d 2);

Погодьтеся, що так набагато коротше і зрозуміліше.

уявлення

Синтаксис створення уявлень ми вже розглядали. Після створення уявлень, їх можна використовувати так само, як таблиці. Тобто виконувати запити до них, фільтрувати і сортувати дані, об'єднувати одні уявлення з іншими. З одного боку це дуже зручний спосіб зберігання частопріменяемих складних запитів (як в нашому прикладі).

Але слід пам'ятати, що уявлення - це не таблиці, тобто вони не зберігають дані, а лише витягують їх з інших таблиць. Звідси, по-перше, при зміні даних в таблицях, результати уявлення так само будуть змінюватися. А по-друге, при запиті до подання відбувається пошук необхідних даних, тобто продуктивність СУБД знижується. Тому зловживати ними не варто.

Функція SUM в SQL-мові, незважаючи на свою простоту, використовується досить часто при роботі з базою даних. З її допомогою зручно отримувати деякі проміжні або підсумкові результати, не вдаючись до допомоги допоміжних інструментаріїв СУБД.

синтаксис функції

У більшості мов SQL синтаксис sum однаковий - як аргумент використовується тільки ім'я поля або якийсь арифметична дія кількох з них, за яким потрібно провести підсумовування.

У виняткових випадках можна передавати конкретне значення у вигляді числа або змінної, проте подібні "схеми" практично не використовуються, тому що не несуть в собі великий цінності. Нижче наведено синтаксис функції на мові SQL:

sum (а) - тут як параметр а використовується деякий числове значення або вираз

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

Приклад використання SUM в SQL

Для остаточного розуміння принципу роботи функції варто розглянути кілька прикладів. У SQL SUM може використовуватися як в якості повертається результату, так і в ролі проміжного значення, наприклад, для перевірки умови.

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

SELECT Товар, sum (СуммаПокупок) FROM Продажі GroupBy Товар;

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

Для другого прикладу необхідно отримати список товарів, сума продажів за якими перевищила деяке значення, наприклад, 100. Отримати результат по цьому завданню можна кількома способами, найбільш оптимальним з яких є виконання одного запиту:

SELECT Товар FROM (SELECT Товар, sum (СуммаПокупок) as Сума FROM Продажі) WHERE Сума\u003e 100.

У цьому навчальному посібнику ви дізнаєтеся, як використовувати MySQL функцію SUM з синтаксисом і прикладами.

опис

MySQL функція SUM повертає сумарне значення виразу.

синтаксис

Синтаксис MySQL функції SUM:

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

Параметри або аргументи

expression1, expression2, ... expression_n - вираження, що не вміщені в функції SUM і повинні бути включені в пропозицію GROUP BY в кінці SQL оператора.

aggregate_expression - це стовпець або вираз, яке буде підсумовуватися.

tables - таблиці, з яких ви хочете отримати записи. Повинна бути хоча б одна таблиця, зазначена в пропозиції FROM.

WHERE conditions - необов'язковий. Це умови, які повинні бути виконані для обраних записів.

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

Функція SUM може використовуватися в наступних версіях MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Приклад з одним виразом

Розглянемо приклади MySQL функції SUM, щоб зрозуміти, як використовувати функцію SUM в MySQL.

Наприклад, вам необхідно дізнатися яка сукупна загальна заробітна плата всіх співробітників, чия зарплата перевищує 20 000 доларів на рік.

У цьому прикладі функції SUM, висловом SUM (salary) присвоїли псевдонім «Total Salary». В результаті при поверненні набору результатів в якості імені поля буде відображатися «Total Salary».

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

Ви можете використовувати пропозицію DISTINCT в функції SUM. Наприклад, наведена нижче інструкція SQL повертає комбінований загальний оклад унікальних значень заробітної плати, де зарплата вище 20 000 доларів на рік.

Якби дві зарплати становили 10 000 доларів на рік, в функції SUM використовувалося б тільки одне з цих значень.

Приклад з використанням формули

Вираз, що міститься в функції SUM, не обов'язково має бути одним полем. Ви також можете використовувати формулу. Наприклад, ви можете розрахувати загальну комісію.

MySQL

SELECT SUM (sales * 0.05) AS "Total Commission" FROM orders;

SELECT SUM (sales * 0.05) AS "Total Commission" FROM orders;

Приклад з використанням GROUP BY

У деяких випадках вам буде потрібно використовувати пропозицію GROUP BY з функцією SUM.

Наприклад, ви можете використовувати функцію SUM, щоб повернути department (назва відділу) і загальний обсяг sales (продажів у відповідному відділі).