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можна використовувати інші вбудовані функції, але їх ми вивчатимемо пізніше.
Вчимося підбивати підсумки. Ні, це ще результати вивчення 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. Для цього пишемо наступний запит:
Запит поверне значення 10 505,90.
І знову вправу для самостійного вирішення. У цьому та деяких інших вправах знадобиться вже не лише таблиця Staff, а й таблиця Org, що містить дані про підрозділи фірми:
приклад 4.До таблиці Staff додається таблиця Org, що містить дані підрозділах фірми. Вивести мінімальну кількість років, опрацьованих одним співробітником у відділі, розташованому у Бостоні.
Функція SQL MAX
Аналогічно працює та має аналогічний синтаксис функція SQL MAX, яка застосовується, коли потрібно визначити максимальне значення серед усіх значень стовпця.
Приклад 5.
Потрібно дізнатися про максимальну заробітну плату співробітників відділу з номером 42. Для цього пишемо наступний запит:
Запит поверне значення 18352,80
Прийшов час вправи для самостійного вирішення.
Приклад 6.Знову працюємо з двома таблицями – Staff та Org. Вивести назву відділу та максимальне значення комісійних, які отримують один співробітник у відділі, що відноситься до групи відділів (Division) Eastern. Використати JOIN (з'єднання таблиць) .
Функція SQL AVG
Зазначене щодо синтаксису для попередніх описаних функцій є правильним і щодо функції SQL AVG. Ця функція повертає середнє серед усіх значень стовпця.
Приклад 7.База даних та таблиця - ті ж, що й у попередніх прикладах.
Нехай потрібно дізнатися середній стаж працівників відділу з номером 42. Для цього пишемо наступний запит:
Результатом буде значення 6,33
Приклад 8.Працюємо з однією таблицею – Staff. Вивести середню зарплату працівників зі стажем від 4 до 6 років.
Функція SQL COUNT
Функція SQL COUNT повертає кількість записів таблиці бази даних. Якщо в запиті вказати SELECT COUNT(ІМ'Я_СТОЛБЦЯ) ..., то результатом буде кількість записів без урахування записів, у яких значенням стовпця є NULL (невизначене). Якщо використовувати як аргумент зірочку і почати запит SELECT COUNT(*) ..., то результатом буде кількість всіх записів (рядків) таблиці.
Приклад 9.База даних та таблиця - ті ж, що й у попередніх прикладах.
Потрібно дізнатися про кількість всіх співробітників, які отримують комісійні. Число співробітників, у яких значення стовпця Comm – не NULL, поверне наступний запит:
SELECT COUNT (Comm) FROM Staff
Результатом буде 11.
приклад 10.База даних та таблиця - ті ж, що й у попередніх прикладах.
Якщо потрібно дізнатися загальну кількість записів у таблиці, то застосовуємо запит зі зірочкою як аргумент функції COUNT:
SELECT COUNT (*) FROM Staff
Результатом буде 17.
В наступному вправі для самостійного вирішенняпотрібно використовувати підзапит.
Приклад 11.Працюємо з однією таблицею – Staff. Вивести кількість співробітників у відділі планування (Plains).
Агрегатні функції разом із SQL GROUP BY (угрупуванням)
Тепер розглянемо застосування агрегатних функцій разом із оператором SQL GROUP BY. Оператор SQL GROUP BY служить для групування результуючих значень стовпчиків таблиці бази даних. На сайті є урок, присвячений окремо цьому оператору .
приклад 12.Існує база даних порталу оголошень. Вона містить таблицю Ads, яка містить дані про оголошення, подані за тиждень. Стовпець Category містить дані про великі категорії оголошень (наприклад, Нерухомість), а стовпець Parts - про дрібніші частини, що входять до категорії (наприклад, частини Квартири та Дачі є частинами категорії Нерухомість). Стовпець Units містить дані про кількість поданих оголошень, а стовпець Money - про грошові суми, отримані за подачу оголошень.
| Category | Part | Units | Money |
| Транспорт | Автомашини | 110 | 17600 |
| Нерухомість | Квартири | 89 | 18690 |
| Нерухомість | Дачі | 57 | 11970 |
| Транспорт | Мотоцикли | 131 | 20960 |
| Будматеріали | Дошки | 68 | 7140 |
| Електротехніка | Телевізори | 127 | 8255 |
| Електротехніка | Холодильники | 137 | 8905 |
| Будматеріали | Регіпс | 112 | 11760 |
| Дозвілля | Книги | 96 | 6240 |
| Нерухомість | Будинки | 47 | 9870 |
| Дозвілля | Музика | 117 | 7605 |
| Дозвілля | Ігри | 41 | 2665 |
Використовуючи оператор SQL GROUP BY, знайти суми грошей, отриманих за подачу оголошень в кожній категорії. Пишемо наступний запит:
SELECT Category, SUM (Money) AS Money FROM Ads GROUP BY Category
приклад 13.База даних та таблиця - та сама, що у попередньому прикладі.
Використовуючи оператор SQL GROUP BY, з'ясувати, в якій частині кожної категорії було подано найбільшу кількість оголошень. Пишемо наступний запит:
SELECT Category, Part, MAX (Units) AS Maximum FROM Ads GROUP BY Category
Результатом буде наступна таблиця:
Підсумкові та індивідуальні значення в одній таблиці можна отримати об'єднанням результатів запитів за допомогою оператора UNION .
Реляційні бази даних та мова SQL
є запит виду:
SELECT i.*, COUNT(*) AS Currence, SUM(ig.quantity) AS total, SUM(g.price *ig.quantity) AS price, c.briefly AS cname FROM invoice AS i, invoice_goods AS ig, good g LEFT JOIN currency c ON (c.id = g.currency) WHERE ig.invoice_id = i.id AND g.id = ig.good_id GROUP BY g.currency ORDER BY i.date DESC;
тобто. вибирається список замовлень, в якому вважаються сумарні вартості товарів у різних валютах (валюта встановлена у товару, стовпець cname в результаті - назва валюти)
потрібно в стовпці результату currencies отримувати кількість записів з однаковим i.id , однак експерименти з параметрами COUNT() ні до чого не привели - завжди повертає 1
Питання: чи можливо отримати справжнє значення у стовпці коррень? Тобто. якщо замовлені товари з цінами в 3х різних валютах, currencies = 3?
Занадто великі вільності допускає MySQL по відношенню до SQL, однак. Що, наприклад, означає i.* у контексті цього селекту? Всі колонки таблиці invoice? Так як до них не застосовується жодна групова функція, то непогано було б, щоб вони були перераховані в GROUP BY, інакше не зовсім зрозумілий принцип угруповання рядків. Якщо необхідно отримати всі товари по всіх замовленнях у розрізі валют, це одне, якщо необхідно отримати всі товари згруповані за валютами у розрізі кожного замовлення, це зовсім інше.
Виходячи з вашого селекту, можна припустити наступну структуру даних:
Таблиця invoice:
Таблиця invoice_goods:
Таблиця goods:
Таблиця currency:
Що поверне ваш поточний селект? За ідеєю він поверне для кожного замовлення N-рядок по кожній валюті, в якій у цьому замовленні є товари. Але через те, що в group by не вказано нічого крім g.currency, це не очевидно:), більше того, колонка c.briefly теж робить свій внесок у неявну освіту груп. Що ж ми маємо в результаті, для кожної унікальної комбінації i.*, g.currency і c.briefly буде сформована група до рядків якої будуть застосовані функції SUM та COUNT. Те, що в результаті гри з параметром COUNT у вас виходила завжди 1 означає, що в результуючій групі був тільки один запис (тобто групи формуються не так як вам, можливо, потрібно, можете описати вимоги докладніше?). З вашого питання не ясно, що ви хотіли б дізнатися - скільки різних валют брало участь у замовленні або скільки замовлень було в цій валюті? У першому випадку можливі кілька варіантів залежить від можливостей mySQL, у другому треба по-іншому написати селект вираз.
Занадто великі вільності допускає MySQL по відношенню до SQL, однак. Що, наприклад, означає i.* у контексті цього селекту? Всі колонки таблиці invoice?
Так саме. Але великої ролі не грає, т.к. корисних у разі серед них (колонок) немає. Нехай i.* буде i.id. Для визначеності.
Що поверне ваш поточний селект? За ідеєю він поверне для кожного замовлення N-рядок по кожній валюті, в якій у цьому замовленні є товари. Але через те, що в group by не вказано нічого крім g.currency, це не очевидно:),
Саме так.
Поверне він наступне (у цьому прикладі з я вибираю тільки id, а не всі стовпці):
| id | currencies | total | price | cname |
|---|---|---|---|---|
| 33 | 1 | 1.00 | 198.00 | BF |
| 33 | 1 | 4.00 | 1548.04 | РУБ |
більше того, колонка c.briefly теж робить свій внесок у неявну освіту груп.
Яким чином? По c.id=g.currency виробляється з'єднання таблиць, а угруповання по g.currency .
Те, що в результаті гри з параметром COUNT у вас виходила завжди 1 означає, що в результуючій групі був тільки один запис
Ні, група будувалася з 1йзапис. Наскільки я це зрозумів, COUNT() повертає 1 саме тому (адже стовпці, які у групі відрізняються (щоправда, крім стовпця валют), створюються аггрегатними функціями).
(Тобто групи формуються не так як вам, можливо, потрібно, можете описати вимоги докладніше?).
Групи формуються так, як треба, кожна група -це сумарна вартість товарів у кожній валюті. Однак, мені, крім того, потрібно порахувати, скількиж елементів уцією групі.
З вашого питання не ясно, що ви хотіли б дізнатися - скільки різних валют брало участь у замовленні або скільки замовлень було в цій валюті?
Так, заробився небагато. Саме перше.
dmig[досьє]
Під "неявною" участю в освіті групи я маю на увазі, що якщо колонка не вказана в GROUP BY, і при цьому НЕ є аргументом групової функції, то результат селекту буде ідентичний тому, якби ця колонка БУЛА вказана в GROUP BY. Ваш селект і селект наведений нижче виведуть абсолютно однаковий результат (не звертайте уваги на join"и, я просто привів їх до єдиного формату запису):
Select i.id id, count(*) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly name FROM invoice i join invoice_goods ig on (ig.invoice_id = i. id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly
Виходить що у кожному рядку результуючої вибірки є одна, і лише одна валюта (якби це було інакше, те й рядків було дві). Про кількість яких елементів у такому разі йдеться? Про пункти замовлення? Тоді ваш селект абсолютно правильний, просто по даній валюті, у цьому замовленні лише один пункт.
Давайте розглянемо схему даних:
- В одному замовленні безліч пунктів (рядків), так?
- Кожен пункт це товар у довіднику goods, так?
- Кожен товар має певну (і лише одну) валюту, це випливає із c.id = g.currency, так?
Скільки валют у замовленні? Стільки скільки в ньому пунктів із РІЗНИМИ валютами.
Складати g.price *ig.quantity має сенс тільки для пунктів в одній валюті;) (хоча кілометри з годинником, теж складати можна:) Так що ж вас не влаштовує!? Ви стверджуєте, що вам потрібно скільки різних валют брало участь у замовленні
і в такому випадку зробити це в рамках цього ж селекту без усіляких хитрощів (які швидше за все не потягне MySQL) не вийде;
Я на жаль не знавець MySQL. В oracle зробити це одним селектом можна, але чи допоможе вам така порада? Навряд чи;)
# В одному замовленні безліч пунктів (рядків), так?
# Кожен пункт це товар у довіднику goods, правда?
# Кожен товар має певну (і лише одну) валюту, це випливає із c.id = g.currency, так?
Так.
Одне замовлення: один запис у таблиці invoice, їй відповідають n(>0) записів в invoice_goods, кожній з яких відповідає 1 запис у таблиці goods, запис "валюта" у кожній з яких, у свою чергу, відповідає 1й запису в таблиці currency ( LEFT JOIN – на випадок редагування довідника валют кривими руками – таблиці типу MyISAM не підтримують зовнішніх ключів).
Скільки валют у замовленні? Стільки скільки в ньому пунктів із РІЗНИМИ валютами.
Так, саме так.
Складати g.price * ig.quantity має сенс тільки для пунктів в одній валюті;) (хоча кілометри з годинником, теж складати можна:)
Саме тому робиться угруповання з id валюти (g.currency).
В oracle зробити це одним селектом можна, але чи допоможе вам така порада?
М.Б.
Я трохи спілкувався з Oracle, з pl/sql знаком.
Варіант №1.
Виберіть a.*, count(*) over (partition by a.id) ціни від (select i.id id, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly) a
Це використовує т.зв. analytic function. З ймовірністю 99% НЕ працює у MySQL.
Варіант №2.
Створюється функція, countCurrencies наприклад, яка за id замовлення повертає кількість валют яке в ньому брало участь і тоді:
Select i.id id, countCurrencies(i.id) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly name FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly, countCurrencies(i.id)
Може прокатати... але викликатиметься для кожної валюти кожного замовлення. Не знаю чи дає MySQL робити GROUP BY за функцією...
Варіант №3
Select i.id id, agr.cnt currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly name FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id ) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) праворуч з join (select ii.id, count(distinct gg.currency) cnt from invoice ii, invoce_goods iig, добре gg, де ii.id = iig.invoice_id і gg.id = iig.good_id group by ii.id) agr on (i.id = agr.id) group by i.id, c.briefly, agr. cnt
Напевно найправильніший... і цілком можливо найробітніший варіант із усіх.
Найшвидший це варіант №1. №2 найнеефективніший, т.к. що більше валют у замовленні, то частіше вони вважаються.
№3 теж у принципі не найкращий за швидкістю, але принаймні можна покластися на кешування всередині СУБД.
результатом всіх трьох селектів буде наступне:
| id | currencies | total | price | cname | ||||
|---|---|---|---|---|---|---|---|---|
| 33 | 2 | 1.00 | 198.00 | BF | ||||
| 33 | 2 | 4.00 | 1548.04 | РУБ | ||||
для одного і того ж id цифра в колонці currencies буде завжди однакова, це те, що вам треба?
Описується використання арифметичних операторів та побудова обчислюваних стовпців. Розглядаються підсумкові (агрегатні) функції 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 без зазначення суми.
У цьому навчальному посібнику ви дізнаєтесь, як використовувати функцію 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'.