6. Групування записів та використання агрегуючих функцій мови sql.

Групування записів

При розгляді деякої таблиці Товари. Кожний з видiв товару, перерахований в нiй, вiдноситься до певної категорiї, а тому записи можна об’єднати в групи на основi значення поля Категорiя. Оскiльки поле Категорiя мiстить 7 рiзних значень, то i груп повинно утворитися також 7. Щоб вказати, що повинно здiйснюватись групування, потрiбно до тексту запиту включити рядок

GROUP BY Категорiя

Це ще не все. Виникає також питання, що включати в запитi до списку полiв? На вiдмiну вiд деяких iнших СУБД, ACCESS вiдмовляється виконувати запит iз групуванням записiв, якщо в списку полiв стоїть * або просто перерахованi окремi поля таблицi. Насправдi до списку полiв можна включати iм’я поля, за яким здiйснюється групування, та агрегуючi функцiї, але в цьому випадку дія агрегуючих функцiй поширюється не на всю таблицю, а лише на записи, якi потрапили до окремої групи.

Для iлюстрацiї сказаного виконаємо такий запит:

SELECT Категорiя,

Count(*) AS Кiлькiсть,

Min(Цiна) AS [Мiнiмальна цiна],

Max(Цiна) AS [Максимальна цiна]

FROM Товари

GROUP BY Категорiя

Результуюча таблиця має 7 рядкiв — по кiлькостi груп. В першому полi йдуть значення категорiй. Ще раз пiдкреслимо, що групування здійснюється за полем Категорiя, а тому в окрему групу потрапляють записи з однаковим значенням поля Категорiя. Функцiя Count(*), застосована до кожної групи, дає кiлькiсть записiв, якi потрапили в окрему групу — цi значення перерахованi в другому стовпцi. Функцiї Min та Max обчислюють мiнiмальне та максимальне значення в полi Цiна для записiв в окремiй групi.

В принципi, групування можна здiйснювати за будь-яким полем таблицi; при цьому буде утворюватись стiльки ж груп, скiльки унiкальних значень мiстить поле. Зокрема, якщо всi значення в полi рiзнi

Дещо модифiкуємо запит, а саме: пiсля ключового слова WHERE додамо умову Цiна<=1000:

SELECT Категорiя,

Count(*) AS Кiлькiсть,

Min(Цiна) AS [Мiнiмальна цiна],

Max(Цiна) AS [Максимальна цiна]

FROM Товари

WHERE Цiна <= 1000

GROUP BY Категорiя

Виконання запиту можна уявити так, нiби спочатку з таблицi вiдбираються всi записи, для яких виконується вказана умова, а потiм над вiдiбраними записами виконуються операцiї групування та обчислення агрегуючих функцiй.

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

Взагалi, умова HAVING дiє аналогiчно умовi WHERE, але застосовується до груп. WHERE накладає умови на записи, а HAVING — на групи, а тому в умовi WHERE не можуть стояти агрегуючi функцiї. Спiльна робота GROUP BY та WHERE вiдбувається наступним чином. Спочатку в таблицi шукаються всi записи, якi вiдповiдають умовам WHERE, а потiм GROUP BY роздiляє вiдiбранi записи на групи. Аж пiсля цього серед створених груп вiдбираються тi, якi задовольняють умовi HAVING.

Агрегуючі функції

Замiсть списку полiв, який фiгурує в командi SELECT, можна вписати спецiальнi функцiї, якi називаються агрегуючими i застосовуються для отримання узагальнюючих значень — наприклад, суми або середнього значення по стовпцю. Найважливiшi серед агрегуючих функцiй такi:

Функцiя	Призначення
Avg(поле)	Середнє арифметичне непустих значень, якi мiстяться у вказаному полi
Count(поле)	Кiлькiсть непустих значень у вказаному полi
Count(*)	Загальна кiлькiсть записiв у таблицi, включаючи записи, якi мiстять пустi значення
Sum(поле)	Сума значень у вказаному полi
Min(поле)	Найменше значення серед значень, якi мiстяться в полi
Max(поле)	Найбiльше значення серед значень, якi мiстяться в полi
First(поле)	Значення поля з першого рядка таблицi
Last(поле)	Значення поля з останнього рядка таблицi

Виконаємо такий запит:

SELECT Count(*) AS [Кiлькiсть записiв],

Sum(Цiна) AS Сума,

Avg(Цiна) AS [Середня цiна]

FROM Товари

Як бачимо, в цiй таблицi три поля: перше мiстить кiлькiсть записiв в таблицi Товари, обчислене за допомогою функцiї Count(*), друге — суму значень з поля Цiна _, третє — середнє значення поля Цiна.