Agréger des données
à l'aide de fonctions de groupe
Objectifs
A la fin de ce chapitre, vous pourrez :
• identifier les fonctions de groupe disponibles
• expliquer l'utilisation des fonctions de groupe
• regrouper des données à l'aide de la clause
GROUP BY
• inclure ou exclure des groupes de lignes à l'aide
de la clause HAVING
Définition des fonctions de groupe
Les fonctions de groupe agissent sur des groupes de
lignes et donnent un résultat par groupe.
EMPLOYEES
Salaire
maximum dans
la table
EMPLOYEES.
…
Types de fonction de groupe
• AVG
• COUNT
• MAX
• MIN
• SUM
Syntaxe des fonctions de groupe
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
Utiliser les fonctions AVG et SUM
Les fonctions AVG et SUM s'utilisent avec des données
numériques.
SELECT AVG(salary)as avg, MAX(salary) max,
MIN(salary) min, SUM(salary) somme
FROM employees
WHERE job_id LIKE '%REP%';
Utiliser les fonctions MIN et MAX
Les fonctions MIN et MAX s'utilisent avec tous les
types de données.
SELECT MIN(hire_date) as minim, MAX(hire_date) max
FROM employees;
Utiliser la fonction COUNT
La fonction COUNT(*) renvoie le nombre de lignes
d'une table
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
Utiliser la fonction COUNT
• La fonction COUNT(expr) renvoie le nombre de
lignes contenant des valeurs non NULL dans la
colonne expr.
• Affichez le nombre de valeurs contenues dans la
colonne DEPARTMENT_ID de la table EMPLOYEES, à
l'exception des valeurs NULL.
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;
Utiliser le mot-clé DISTINCT
• La fonction COUNT(DISTINCT expr) renvoie le
nombre de valeurs non NULL distinctes de la
colonne expr.
• Affichez le nombre de services distincts contenus
dans la table EMPLOYEES.
SELECT COUNT(DISTINCT department_id)
FROM employees;
Fonctions de groupe et valeurs NULL
Les fonctions de groupe ignorent les valeurs NULL
des colonnes.
SELECT AVG(commission_pct)
FROM employees;
Utiliser la fonction ISNULL
avec les fonctions de groupe
La fonction ISNULL contraint les fonctions de
groupe à intégrer des valeurs NULL.
SELECT AVG(ISNULL(commission_pct, 0))
FROM employees;
Créer des groupes de données
EMPLOYEES
4400
9500
Salaire
moyen
3500 par
service
dans
6400 la
table
EMPLOYEES.
10033
…
Créer des groupes de données :
syntaxe de la clause GROUP BY
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
La clause GROUP BY permet d'organiser les lignes d'une
table en groupes restreints.
Utiliser la clause GROUP BY
La clause GROUP BY doit inclure toutes les colonnes
de la liste SELECT qui ne figurent pas dans des
fonctions de groupe.
SELECT department_id, AVG(salary)
FROM employees where department_id is not null
GROUP BY department_id ;
Utiliser la clause GROUP BY
La colonne GROUP BY ne doit pas nécessairement
figurer dans la liste SELECT.
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
Créer des sous-groupes
EMPLOYEES
Dans la table
EMPLOYEES,
calcul du total
des salaires
pour chaque
poste,
au sein de
chaque service.
…
Utiliser la clause GROUP BY
sur plusieurs colonnes
SELECT department_id dept_id, job_id, SUM(salary) as som
FROM employees
GROUP BY department_id, job_id ;
Erreurs d'utilisation des fonctions
de groupe dans une interrogation
Toute colonne ou expression de la liste SELECT autre
qu'une fonction d'agrégation doit être incluse dans la
clause GROUP BY.
SELECT department_id, COUNT(last_name)
FROM employees;
SELECT department_id, COUNT(last_name)
*
ERROR at line 1:
ORA-00937: not a single-group group function
Erreurs d'utilisation des fonctions
de groupe dans une interrogation
• Vous ne pouvez pas utiliser la clause WHERE pour limiter
les groupes.
• Utilisez la clause HAVING.
• Vous ne pouvez pas utiliser de fonctions de groupe
dans la clause WHERE.
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
WHERE AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Exclure des groupes de résultats
EMPLOYEES
Salaire maximum
par service,
à condition
qu'il soit supérieur
à 10 000 $
…
Exclure des groupes de résultats :
clause HAVING
Utilisez la clause HAVING pour restreindre les groupes.
1. Les lignes sont regroupées.
2. La fonction de groupe est appliquée.
3. Les groupes qui correspondent à la clause HAVING
s'affichent.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Utiliser la clause HAVING
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
Utiliser la clause HAVING
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
Imbriquer des fonctions
de groupe
Affichez le salaire moyen maximum.
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
Synthèse
Ce chapitre vous à permis d'apprendre à :
• utiliser les fonctions de groupe COUNT, MAX, MIN, AVG
• écrire des instructions contenant la clause GROUP BY
• écrire des intructions contenant la clause HAVING
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Présentation de l'exercice 5
Dans cet exercice, vous allez :
• écrire des instructions contenant des fonctions de
groupe
• grouper des lignes pour obtenir plusieurs
résultats
• exclure des groupes en utilisant la clause HAVING
