4. Агрегатні функції мови sql

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

• count - повертає число рядків і звичайно використовується для визначення загального числа рядків у результуючому наборі. Це єдина агрегатна функція, що можна застосовувати для нечисловых стовпчиків.

- sum - повертає суму всіх значень у стовпчику.

- avg - повертає середнє для всіх значень у стовпчику.

• мах - повертає найбільше значення в стовпчику.

• min - повертає найменше значення в стовпчику.

Такий оператор SQL демонструє всі п'ять агрегатних функцій.

SELECT COUNT(Units) AS UnitCount, AVG(Units) AS UnitAvg, SUM(Units) AS UnitSum, MIN(Units) AS UnitMin, MAX(Units) AS UnitMax FROM BookSales;

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

SELECT COUNT(Units) AS UnitCount, AVG(Units) AS UnitAvg, SUM(Units) AS UnitSum, MIN(Units) AS UnitMin, MAX(Units) AS UnitMax FROM BookSales WHERE Qtr =1

5. Оператор group by

Оператор GROUP BY (групувати по) використовується для групування записів за значеннями одного або декількох стовпців. Якщо в SQL-вирзі використовується оператор WHERE, задаючий фільтр записів, то оператор GROUP BY знаходиться і виконується після нього. Для визначення, які записи повинні увійти в групи, служить оператор HAVING, що використовується разом з GROUP BY. Якщо оператор HAVING не застосовується, то групуванню підлягають всі записи, відфільтровані оператором WHERE. ЯКЩО WHERE не використовується, то групуються всі записи вихідної таблиці.

Припустимо, що на основі таблиці Publishers треба згрупувати дані по регіонах. Для цього можна скористатися наступним SQL-виразом:

SELECT Name, Company, StateProv FROM Publishers GROUP BY StateProv;

При цьому записи з однаковими назвами регіонів розташовані поруч один з одним (в одній групі).

Наприклад, ви хочете створити набір даних, що містить назву книги і сумарна кількість її проданих примірників. Це можна зробити за допомогою такого запиту (див. мал. 8.25):

SELECT Title, SUM(Units) AS UnitsSold FROM BookSales GROUP BY Title;

Пропозиція group by потребує, щоб усі числові стовпчики в списку полів вибірки оператора select були аргументами який-небудь агрегатної функції мови SQL (sum, avg, min, max або count). Крім того, у цьому випадку в списку полів вибірки оператора select не можна використовувати зірочку (*).

Оператор GROUP BY збирає записи в групи і впорядковує (сортує) групи за алфавітом (точніше, за ASCII-кодами символів).

6. Оператор having

Оператор HAVING (що мають, за умови) зазвичай використовується разом з оператором груповання GROUP BY і задає фільтр записів в групах. Оператор having діє точно так само, як і рператор where, із тієї лише різницею, що дія оператора having поширюється на результуючі стовпчики, створені пропозицією group by, а не на стовпчики фізичної таблиці.

Нехай, ви хочете одержати список назв усіх книг, проданих за рік у кількості більш 100 примірників.

SELECT Title, SUM(Units) AS UnitsSold FROM BookSales GROUP BY Title HAVING SUM(Units)>100

Стовпчики, використовувані в пропозиції having, не обов'язково повинні бути присутнім у списку полів вибірки оператора select. Структура пропозиції having підпорядковується тим же правилам, що і структура пропозиції where. У ньому ви можете використовувати логічні оператори and, or і not. Такий запит SQL повертає суми продажів по всім книгах, проданим у кількості більш 100 примірників, найменування яких містять у другій позиції букву а.

SELECT Title, SUM(Sales) AS SalesAmt FROM BookSales GROUP BY Title HAVING SUM(Units)>100 AND Title Like ’_a%’;

Якщо в SQL-виразі оператора GROUP BY немає, то оператор HAVING застосовується до всіх записів, що повертається оператором WHERE. Якщо ж відсутня і WHERE, TO HAVING діє на всі записи таблиці.