Sql сума з умовою. Агрегатні функції SQL – SUM, MIN, MAX, AVG, COUNT. Параметри чи аргументи
Як дізнатися кількість моделей ПК, що випускаються тим чи іншим постачальником? Як визначити середнє значення ціни на комп'ютери, що мають однакові технічні характеристики? На ці та інші питання, пов'язані з деякою статистичною інформацією, можна отримати відповіді за допомогою підсумкових (агрегатних) функцій. Стандартом передбачені такі агрегатні функції:
Усі ці функції повертають єдине значення. При цьому функції COUNT, MINі MAXзастосовні до будь-яких типів даних, у той час як SUMі AVGвикористовуються лише для числових полів. Різниця між функцією COUNT(*)і COUNT(<имя поля>) полягає в тому, що друга за підрахунком не враховує NULL-значення.
приклад. Знайти мінімальну та максимальну ціну на персональні комп'ютери:
приклад. Знайти наявну кількість комп'ютерів, випущених виробником А:
приклад. Якщо ж нас цікавить кількість різних моделей, що випускаються виробником А, запит можна сформулювати наступним чином (користуючись тим фактом, що в таблиці Product кожна модель записується один раз):
приклад. Знайти кількість наявних різних моделей, що випускаються виробником А. Запит схожий на попередній, в якому потрібно визначити загальну кількість моделей, що випускаються виробником А. Тут же потрібно знайти число різних моделей в таблиці PC (тобто наявних у продажу).
Для того, щоб при отриманні статистичних показників використовувалися тільки унікальні значення, аргументі агрегатних функційможна використовувати параметр DISTINCT. Інший параметр ALLвикористовується за умовчанням і передбачає підрахунок всіх значень, що повертаються в стовпці. Оператор,
Якщо нам потрібно отримати кількість моделей ПК, вироблених кожнимвиробником, то потрібно використовувати пропозиція GROUP BY, синтаксично наступного після пропозиції WHERE.
Пропозиція GROUP BY
Пропозиція GROUP BYвикористовується визначення груп вихідних рядків, яких можуть застосовуватися агрегатні функції (COUNT, MIN, MAX, AVG та SUM). Якщо ця пропозиція відсутня, і використовуються агрегатні функції, то всі стовпці з іменами, згаданими в SELECT, повинні бути включені до агрегатні функції, і ці функції будуть застосовуватися до всього набору рядків, які відповідають предикату запиту. В іншому випадку всі стовпці списку SELECT, не ввійшлив агрегатні функції повинні бути вказані у пропозиції GROUP BY. У результаті всі вихідні рядки запиту розбиваються на групи, що характеризуються однаковими комбінаціями значень цих стовпцях.Після цього до кожної групи буде застосовано агрегатні функції. Слід пам'ятати, що з GROUP BY всі значення NULL трактуються як рівні, тобто. при групуванні поля, що містить NULL-значення, всі такі рядки потраплять в одну групу. Якщоза наявності пропозиції GROUP BY , у пропозиції SELECTвідсутні агрегатні функції
, то запит просто поверне по одному рядку з кожної групи. Цю можливість, поряд із ключовим словом DISTINCT, можна використовувати для виключення дублікатів рядків у результуючому наборі.
| Розглянемо простий приклад: SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC |
GROUP BY model;
| У цьому запиті кожної моделі ПК визначається їх кількість і середня вартість. Усі рядки з однаковими значеннями model (номер моделі) утворюють групу, і на виході SELECT обчислюються кількість значень та середні значення ціни кожної групи. Результатом виконання запиту буде наступна таблиця: | model | Qty_model |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Avg_price
Якби в SELECT був присутній стовпець з датою, можна було б обчислювати ці показники кожної конкретної дати. Для цього потрібно додати дату як групуючий стовпчик, і тоді агрегатні функції обчислювалися б для кожної комбінації значень (модель-дата). Існує кілька певних:
- правил виконання агрегатних функцій Якщо в результаті виконання запитуне отримано жодного рядка
- (або одного рядка для цієї групи), то вихідні дані для обчислення будь-якої з агрегатних функцій відсутні. І тут результатом виконання функцій COUNT буде нуль, а результатом від інших функцій - NULL.Аргумент агрегатної функціїне може сам містити агрегатні функції
- (функція від функції). Тобто. в одному запиті не можна, скажімо, одержати максимум середніх значень. Результат виконання функції COUNT єціле число
- (INTEGER). Інші агрегатні функції успадковують типи даних значень, що обробляються. Якщо при виконанні функції SUM був отриманий результат, що перевищує максимальне значення типу даних, що використовується, виникає.
помилка Отже, якщо запит не міститьпропозиції GROUP BY агрегатні функції, то , включені в, виконуються над усіма результуючими рядками запиту. Якщо запит містить пропозиція GROUP BY, кожен набір рядків, який має однакові значення стовпця або групи стовпців, заданих у пропозиції GROUP BY, становить групу, та агрегатні функціївиконуються кожної групи окремо.
Пропозиція HAVING
Після цього до кожної групи буде застосовано агрегатні функції. Слід пам'ятати, що з GROUP BY всі значення NULL трактуються як рівні, тобто. при групуванні поля, що містить NULL-значення, всі такі рядки потраплять в одну групу. пропозиція WHEREвизначає предикат для фільтрації рядків, то пропозиція HAVINGзастосовується після угрупованнядля визначення аналогічного предикату, що фільтрує групи за значеннями агрегатних функцій. Ця пропозиція потрібна для перевірки значень, які отримані за допомогою агрегатної функціїне з окремих рядків джерела записів, визначеного в пропозиції FROM, а з груп таких рядків. Тому така перевірка не може утримуватись у пропозиції WHERE.
Функція SUM у SQL-мові, незважаючи на свою простоту, використовується досить часто при роботі з базою даних. З її допомогою зручно отримувати деякі проміжні чи підсумкові результати, не вдаючись до допомоги допоміжних інструментаріїв СУБД.
Синтаксис функції
У більшості мов SQLсинтаксис sum однаковий - як аргумент використовується лише ім'я поля або деяка арифметична дія кількох з них, за яким потрібно провести підсумовування.
У виняткових випадках можна передавати конкретне значення у вигляді числа чи змінної, проте подібні "схеми" практично не використовуються, тому що не несуть у собі великої цінності. Нижче наведено синтаксис функції на мові SQL:
sum(а) - тут як параметр а використовується деяке числове значення або вираз
Варто зазначити, що перед параметром можна встановлювати ключові слова, наприклад, DISTINCT або ALL, які братимуть лише унікальні чи всі значення відповідно.
Приклад використання SUM у SQL
Для остаточного розуміння принципу роботи функції варто розглянути кілька прикладів. SQL SUM може використовуватися як як повертається результату, так і в ролі проміжного значення, наприклад, для перевірки умови.
Для першого випадку розглянемо варіант, коли потрібно повернути суму продажів по кожному товару, враховуючи, що кількість зроблених покупок може бути у множині. Для отримання результату достатньо виконати наступний запит:
SELECT Товар, sum(СумаПокупок) FROM Продаж GroupBy Товар;
Відповіддю на цю командубуде унікальний список товарів із підсумковою сумою покупок по кожному з них.
Для другого прикладу необхідно отримати список товарів, сума продажів за якими перевищила деяке значення, наприклад, 100. Отримати результат за даним завданням можна декількома способами, найбільш оптимальним з яких є виконання одного запиту:
SELECT Товар FROM (SELECT Товар, sum(СумаПокупок) as Сума FROM Продажу) WHERE Сума > 100.
SQL - Урок 11. Підсумкові функції, що обчислюються стовпці та уявлення
Підсумкові функції ще називають статистичними, агрегатними або підсумовуючими. Ці функції обробляють набір рядків для підрахунку та повернення одного значення. Таких функцій лише п'ять:- AVG() Функція повертає середнє стовпця.
- COUNT() Функція повертає кількість рядків у стовпці.
- MAX() Функція повертає найбільше значення у стовпці.
- MIN() Функція повертає найменше значення у стовпці.
- SUM() Функція повертає суму значень стовпця.
SELECT MIN(price), MAX(price), AVG(price) FROM prices;
Тепер, ми хочемо дізнатися, яку суму нам привіз товар постачальник " Будинок друку " (id=2). Скласти такий запит не так просто. Давайте поміркуємо, як його скласти:
1. Спочатку треба з таблиці Поставки (incoming) вибрати ідентифікатори (id_incoming) поставок, які здійснювалися постачальником "Будинок друку" (id=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= prices.id_product AND id vendor = 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= FROM incoming WHERE id_vendor=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, ціни AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming WHERE id_vendor = 2);
Погодьтеся, що так набагато коротше і зрозуміліше.
Уявлення
Синтаксис створення вистав ми вже розглядали. Після створення уявлень їх можна використовувати так само, як таблиці. Тобто виконувати запити до них, фільтрувати та сортувати дані, поєднувати одні уявлення з іншими. З одного боку це дуже зручний спосіб зберігання часто застосовуваних складних запитів(як у прикладі).Але слід пам'ятати, що уявлення - це таблиці, тобто де вони зберігають дані, лише витягують їх із інших таблиць. Звідси, по-перше, при зміні даних у таблицях, результати подання також змінюватимуться. А по-друге, при запиті до подання відбувається пошук необхідних даних, тобто продуктивність СУБД знижується. Тому зловживати ними не варто.
В цьому навчальному посібникуви дізнаєтесь, як використовувати функцію SUMв SQL Server(Transact-SQL) з синтаксисом та прикладами.
Опис
У SQL Server (Transact-SQL) функція SUMповертає сумарне значення виразу.
Синтаксис
Синтаксис функції SUM у SQL Server (Transact-SQL):
АБО синтаксис функції SUM при групуванні результатів за одним або декількома стовпцями:
Параметри чи аргументи
expression1 , expression2 , … expression_n — вирази, які не включені до функції SUM і повинні бути включені до оператора GROUP BY в кінці SQL-пропозиції.
aggregate_expression – це стовпець або вираз, який буде підсумовований.
tables — таблиці, з яких потрібно отримати записи. Має бути хоча б одна таблиця, перерахована в операторі FROM.
WHERE conditions – необов'язковий. Це умови, які потрібно виконувати для вибраних записів.
Застосування
Функція SUM може використовуватися в наступних версіях 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 функції SUM, щоб зрозуміти, як використовувати функцію SUM у SQL Server (Transact-SQL).
Наприклад, ви можете дізнатися, як загальна кількість всіх продуктів, кількість яких більше 10.
У цьому прикладі функції SUM ми виразу SUM (quantity) встановили псевдонім Total Quantity. При поверненні результуючого набору – «Total Quantity» відображатиметься як ім'я поля.
Приклад використання DISTINCT
Ви можете використовувати оператор DISTINCT у функції SUM. Наприклад, наведений нижче оператор SQLповертає загальну суму salary з унікальними значеннями salary , де salary нижче 29 000 доларів на рік.
Якби дві salary становили 24 000 дол. на рік, у функції SUM використовувалося лише одне з цих значень.
Приклад використання формули
Вираз, що міститься у функції SUM, не обов'язково має бути одним полем. Ви також можете використати формулу. Наприклад, ви можете розрахувати спільну комісію.
Transact-SQL
SELECT SUM(sales * 0.03) AS "Total Commission" FROM orders;
SELECT SUM (sales * 0.03) AS "Total Commission" FROM orders; |
Приклад використання GROUP BY
У деяких випадках вам потрібно буде використовувати оператор GROUP BY з функцією SUM.
Вчимося підбивати підсумки. Ні, це ще результати вивчення SQL, а підсумки значень стовпців таблиць бази даних. Агрегатні функції SQL діють щодо значень шпальти з метою отримання єдиного результуючого значення. Найчастіше застосовуються агрегатні функції SQL SUM, MIN, MAX, AVG та COUNT. Слід розрізняти два випадки застосування агрегатних функцій. Перший: агрегатні функції використовуються власними силами і повертають одне результуюче значення. Другий: агрегатні функції використовуються з оператором SQL GROUP BY, тобто з групуванням по полях (стовпцям) для отримання результуючих значень у кожній групі. Розглянемо спочатку випадки використання агрегатних функцій без угруповання.
Функція SQL SUM
Функція SQL SUM повертає суму значень шпальти таблиці бази даних. Вона може застосовуватися тільки до стовпців, значення яких числа. Запити SQLдля отримання результуючої суми починаються так:
SELECT SUM (ІМ'Я_СТОЛБЦЯ) ...
Після цього виразу слідує FROM (ІМ'Я_ТАБЛИЦІ), а далі за допомогою конструкції WHERE може бути задана умова. Крім того, перед іменем стовпця може бути вказано DISTINCT, і це означає, що враховуватимуться лише унікальні значення. За умовчанням враховуються всі значення (для цього можна особливо вказати не DISTINCT, а ALL, але слово ALL не є обов'язковим).
Якщо ви хочете виконати запити до бази даних цього уроку на MS SQL Server, але ця СУБД не встановлена на вашому комп'ютері, то її можна встановити, користуючись інструкцією з цього посилання .
Спочатку працюватимемо з базою даних фірми - Company1. Скрипт для створення цієї бази даних, її таблиць та заповнення таблиць даними - у файлі за цим посиланням .
приклад 1.Є база даних фірми з даними про її підрозділи та співробітників. Таблиця Staff також має стовпець з даними про заробітну плату співробітників. Вибірка з таблиці має такий вигляд (для збільшення картинки клацнути по ній лівою кнопкою миші):
Для отримання суми розмірів усіх заробітних плат використовуємо наступний запит (на MS SQL Server - з попередньою конструкцією USE company1;):
SELECT SUM (Salary) FROM Staff
Цей запит поверне значення 287664,63.
А зараз . У вправах вже починаємо ускладнювати завдання, наближаючи їх до тих, що зустрічаються практично.
Функція SQL MIN
Функція SQL MIN також діє щодо стовпців, значеннями яких є числа та повертає мінімальне серед усіх значень стовпця. Ця функція має синтаксис, аналогічний синтаксису функції SUM.
приклад 3.База даних та таблиця - ті ж, що й у прикладі 1.
Потрібно дізнатися мінімальну заробітну платуспівробітників відділу з номером 42. Для цього пишемо наступний запит (на MS SQL Server - з попередньою конструкцією USE company1;):
Запит поверне значення 10 505,90.
І знову вправа для самостійного рішення . У цьому та деяких інших вправах знадобиться вже не лише таблиця Staff, а й таблиця Org, що містить дані про підрозділи фірми:
приклад 4.До таблиці Staff додається таблиця Org, що містить дані підрозділах фірми. Вивести мінімальну кількість років, опрацьованих одним співробітником у відділі, розташованому у Бостоні.
Функція SQL MAX
Аналогічно працює та має аналогічний синтаксис функція SQL MAX, яка застосовується, коли потрібно визначити максимальне значення серед усіх значень стовпця.
Приклад 5.
Потрібно дізнатися максимальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит (на MS SQL Server - з попередньою конструкцією USE company1;):
Запит поверне значення 18352,80
Прийшов час вправи для самостійного вирішення.
Приклад 6.Знову працюємо з двома таблицями – Staff та Org. Вивести назву відділу та максимальне значення комісійних, які отримують один співробітник у відділі, що відноситься до групи відділів (Division) Eastern. Використати JOIN (з'єднання таблиць) .
Функція SQL AVG
Зазначене щодо синтаксису для попередніх описаних функцій є правильним і щодо функції SQL AVG. Ця функція повертає середнє серед усіх значень стовпця.
Приклад 7.База даних та таблиця - ті ж, що й у попередніх прикладах.
Нехай потрібно дізнатися середній стаж працівників відділу з номером 42. Для цього пишемо наступний запит (на MS SQL Server - з попередньою конструкцією USE company1;):
Результатом буде значення 6,33
Приклад 8.Працюємо з однією таблицею – Staff. Вивести середню зарплату працівників зі стажем від 4 до 6 років.
Функція SQL COUNT
Функція SQL COUNT повертає кількість записів таблиці бази даних. Якщо в запиті вказати SELECT COUNT(ІМ'Я_СТОЛБЦЯ) ..., то результатом буде кількість записів без урахування записів, у яких значенням стовпця є NULL (невизначене). Якщо використовувати як аргумент зірочку і почати запит SELECT COUNT(*) ..., то результатом буде кількість всіх записів (рядків) таблиці.
Приклад 9.База даних та таблиця - ті ж, що й у попередніх прикладах.
Потрібно дізнатися про кількість всіх співробітників, які отримують комісійні. Число співробітників, у яких значення стовпця Comm – не NULL, поверне наступний запит (на MS SQL Server – з попередньою конструкцією USE company1;):
SELECT COUNT (Comm) FROM Staff
Результатом буде 11.
Приклад 10База даних та таблиця - ті ж, що й у попередніх прикладах.
Якщо потрібно дізнатися загальну кількість записів у таблиці, то застосовуємо запит зі зірочкою як аргумент функції COUNT (на MS SQL Server - з попередньою конструкцією USE company1;):
SELECT COUNT (*) FROM Staff
Результатом буде значення 17.
В наступному вправі для самостійного вирішенняпотрібно використовувати підзапит.
Приклад 11.Працюємо з однією таблицею – Staff. Вивести кількість співробітників у відділі планування (Plains).
Агрегатні функції разом із SQL GROUP BY (угрупуванням)
Тепер розглянемо застосування агрегатних функцій разом із оператором SQL GROUP BY. Оператор SQL GROUP BY служить для групування результуючих значень стовпчиків таблиці бази даних. На сайті є урок, присвячений окремо цьому оператору .
Працюватимемо з базою даних "Портал оголошень 1". Скрипт для створення цієї бази даних, її таблиці та заповнення таблиці даних – у файлі за цим посиланням .
Приклад 12Отже, є база даних порталу об'яв. Вона містить таблицю Ads, яка містить дані про оголошення, подані за тиждень. Стовпець Category містить дані про великі категорії оголошень (наприклад, Нерухомість), а стовпець Parts - про дрібніші частини, що входять до категорії (наприклад, частини Квартири та Дачі є частинами категорії Нерухомість). Стовпець Units містить дані про кількість поданих оголошень, а стовпець Money - про грошові суми, отримані за подачу оголошень.
| Category | Part | Units | Money |
| Транспорт | Автомашини | 110 | 17600 |
| Нерухомість | Квартири | 89 | 18690 |
| Нерухомість | Дачі | 57 | 11970 |
| Транспорт | Мотоцикли | 131 | 20960 |
| Будматеріали | Дошки | 68 | 7140 |
| Електротехніка | Телевізори | 127 | 8255 |
| Електротехніка | Холодильники | 137 | 8905 |
| Будматеріали | Регіпс | 112 | 11760 |
| Дозвілля | Книги | 96 | 6240 |
| Нерухомість | Будинки | 47 | 9870 |
| Дозвілля | Музика | 117 | 7605 |
| Дозвілля | Ігри | 41 | 2665 |
Використовуючи оператор SQL GROUP BY, знайти суми грошей, отриманих за подачу оголошень в кожній категорії. Пишемо наступний запит (на MS SQL Server - з попередньою конструкцією USE adportal1;):
SELECT Category, SUM (Money) AS Money FROM ADS GROUP BY Category
приклад 13.База даних та таблиця - та сама, що у попередньому прикладі.
Використовуючи оператор SQL GROUP BY, з'ясувати, в якій частині кожної категорії було подано найбільшу кількість оголошень. Пишемо наступний запит (на MS SQL Server - з попередньою конструкцією USE adportal1;):
SELECT Category, Part, MAX (Units) AS Maximum FROM ADS GROUP BY Category
Результатом буде наступна таблиця:
Підсумкові та індивідуальні значення в одній таблиці можна отримати об'єднанням результатів запитів за допомогою оператора UNION .
Реляційні бази даних та мова SQL
