Apprendre à construire les agrégations de données dans une base de données de BI

Les bases de données de Business Intelligence sont confrontées à des problématiques récurrentes de calculs, de statistiques, ou de diverses agrégations de données. Beaucoup s'imaginent que faire des sommes et autres moyennes de notes n'est qu'appliquer de simples formules mathématiques. Or, lorsqu'il s'agit de bases de données, ces calculs prennent une autre dimension, dont la complexité dépend des règles de gestion et du besoin client.

Les agrégations, malgré ce nom un peu effrayant, sont faciles à comprendre et à utiliser. Le tableau, ci-dessous, présente les fonctions d'agrégation les plus communes.

;WITH ListUsers AS ( 
SELECT * 
  FROM ( VALUES( 1, 'Georges', 74 ), 
               ( 2, 'Pierre' , 17 ), 
               ( 3, 'Bernard', 63 ), 
               ( 4, 'John'   , 23 ) ) AS Users( Id, Nom, Age ) ) 
  
SELECT SUM( Age ) AS [Somme_Age] 
     , AVG( Age ) AS [Moyenne_Age] 
     , MIN( Age ) AS [Age_Minimum] 
     , MAX( Age ) AS [Age_Maximum] 
     , COUNT( * ) AS [Nombre_Users] 
  FROM ListUsers

Cet exemple utilise un jeu de données assez simple : une liste de personnes avec leur âge, sur laquelle sont faits les différents calculs requis. En premier, la somme des âges, ensuite la moyenne d'âge, puis le plus jeune, le plus vieux et enfin le nombre de lignes dans notre jeu de données.

Ces fonctionnalités ne sont pas difficiles à comprendre. Ce qui peut compliquer la tâche, c'est la difficulté d'associer un certain nombre de colonnes à des calculs spécifiques. Ce que l'on nommera le groupement de données (GROUP BY en langage SQL).

Cette fonctionnalité de la norme SQL permet de définir des éléments communs de regroupement de l'information.

Avant de rentrer dans le texte un peu théorique, pas toujours clair et parfois difficile à comprendre, un petit cas pratique.

CREATE TABLE dbo.TB_DEPARTEMENTS ( 
  ID_DEPARTEMENT  INT PRIMARY KEY 
, NOM_DEPARTEMENT VARCHAR( 50 ) ); 
GO 
  
CREATE TABLE dbo.TB_EMPLOYES ( 
  ID_EMPLOYE      INT PRIMARY KEY 
, NOM_EMPLOYE     VARCHAR( 50 ) NOT NULL 
, AGE_EMPLOYE     INT 
, DEPARTEMENT_ID  INT 
, SALAIRE_EMPLOYE MONEY ); 
GO 
  
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 1, 'Administration'       ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 2, 'Ressources Humaines'  ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 3, 'Service Informatique' ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 4, 'Comptabilité'         ); 
  
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 1, 'Georges', 74, 4, 2480.3 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 2, 'Pierre' , 17, 3, 1387.2 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 3, 'Bernard', 63, 1, 3499.8 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 4, 'John'   , 23, 3, 1876.9 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 5, 'Jérome' , 45, 2, 2286.6 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 6, 'Lina', 30, 3, 2230.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 7, 'Marie', 26, 3, 1980.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 8, 'Virginie', 37, 3, 2730.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 9, 'Hélène', 33, 3, 2430.4 );

Les commandes ci-dessus créent deux tables employés/départements liées par l'id_departement.

Nous sommes ici dans le cadre d'une entreprise qui va vouloir faire quelques statistiques sur ses employés.

V-A. Faire la somme des salaires par département▲

 

Sélectionnez

SELECT NOM_DEPARTEMENT 
     , SUM( SALAIRE_EMPLOYE ) AS TOTAL_SALAIRE 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.TB_DEPARTEMENTS D ON E.DEPARTEMENT_ID = D.ID_DEPARTEMENT 
 GROUP BY NOM_DEPARTEMENT

Vous pourrez remarquer l'emploi de la fonction GROUP BY associée à la colonne NOM_DEPARTEMENT.

Vous constaterez que la valeur de la colonne TOTAL_SALAIRE est égale à la somme totale des salaires saisis pour la liste des employés.

V-B. Explication▲

V-C. Calculer la moyenne des salaires, le salaire minimum et maximum, ainsi que le nombre d'employés par département ▲

 

Sélectionnez

SELECT NOM_DEPARTEMENT 
     , SUM( SALAIRE_EMPLOYE ) AS TOTAL_SALAIRE 
     , AVG( SALAIRE_EMPLOYE ) AS MOYENNE_SALAIRE 
     , MAX( SALAIRE_EMPLOYE ) AS SALAIRE_MAXIMUM 
     , MIN( SALAIRE_EMPLOYE ) AS SALAIRE_MINIMUM 
     , COUNT( * )             AS TOTAL_EMPLOYE 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.TB_DEPARTEMENTS D ON E.DEPARTEMENT_ID = D.ID_DEPARTEMENT 
 GROUP BY NOM_DEPARTEMENT

Ici, en étudiant, on constate certains écarts, notamment au niveau du service informatique là où il y a plusieurs employés.

• La requête permet ici de calculer plusieurs types d'informations basées sur le NOM_DEPARTEMENT comme critère commun.

Il m'est souvent arrivé d'avoir des demandes concernant des requêtes « fausses » à cause d'une mauvaise gestion du GROUP BY. Pour faire au plus simple quant à sa pratique, il faut savoir que toute colonne se trouvant dans un SELECT et n'appartenant pas à une fonction d'agrégation doit se retrouver dans la clause du GROUP BY.

Ce qui est le plus difficile, c'est l'identification des colonnes du SELECT hors agrégat. Il est parfois nécessaire de retourner la question posée afin d'être le plus précis possible sur les colonnes.

• À noter qu'il est possible d'agréger des données sur le résultat d'une fonction.

V-C-1. Exemple▲

 

Sélectionnez

CREATE TABLE dbo.INTERVENTION_CLIENT ( 
  ID_INTERVENTION   INT PRIMARY KEY 
, DATE_INTERVENTION Date 
, NOM_CLIENT        VARCHAR( 50 ) 
, EMPLOYE_ID        INT 
, FACTURATION       Money ) 
  
  
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  1, '2014-01-02', 'Client 1', 2, 500 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  2, '2014-01-03', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  3, '2014-01-06', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  4, '2014-01-07', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  5, '2014-01-08', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  6, '2014-01-09', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  7, '2014-01-10', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  8, '2014-01-03', 'Client 1', 4, 400 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  9, '2014-01-04', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 10, '2014-01-06', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 11, '2014-01-07', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 12, '2014-01-08', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 13, '2014-01-03', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 14, '2014-01-04', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 15, '2014-01-06', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 16, '2014-01-07', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 17, '2014-01-08', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 18, '2014-01-09', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 19, '2014-01-10', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 20, '2014-01-06', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 21, '2014-01-07', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 22, '2014-01-08', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 23, '2014-01-09', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 24, '2014-01-10', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 25, '2014-01-06', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 26, '2014-01-07', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 27, '2014-01-08', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 28, '2014-01-09', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 29, '2014-01-10', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 30, '2014-01-03', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 31, '2014-01-06', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 32, '2014-01-07', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 33, '2014-01-08', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 34, '2014-01-09', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 35, '2014-01-10', 'Client 4', 9, 650 );

Nous créons ici un ensemble de lignes d'intervention de nos collaborateurs chez les clients. Nous souhaitons avoir des informations sur les mois de travail.

V-D. Quelle est la somme des facturations de nos collaborateurs pour les mois écoulés, sans tenir compte du lieu de l'intervention ?▲

 

Sélectionnez

SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE, MONTH( DATE_INTERVENTION );

Nous avons pris en compte trois éléments de la demande :

• mois d'intervention ;
• collaborateur ;
• somme des facturations.

Ces trois informations permettent d'identifier le groupement nécessaire ainsi que l'agrégation souhaitée.

Nous nous retrouvons avec la fonction MONTH() qui permet de ressortir le mois d'une date, dans le SELECT. Contrairement au ORDER BY qui peut fonctionner avec la position de la colonne dans le SELECT, GROUP BY nécessite de reproduire exactement les mêmes commandes que lors du SELECT.

Pour faciliter l'écriture d'un GROUP BY, une CTE, une table dérivée ou une l'utilisation d'une vue, sont de bonnes solutions.

Maintenant que nous savons faire un groupement de données afin de l'agréger, nous allons voir comment exploiter le résultat de notre groupement et comment limiter les résultats à nos attentes.

Nous l'avons vu plus haut, il est possible de calculer des sommes d'interventions d'une personne. Maintenant, imaginons que les consultants envoyés en missions ont un coût pour l'entreprise. Ce coût, c'est le salaire.

À partir de ces deux informations que sont le salaire et les interventions, nous pouvons en déduire quel consultant est « rentable » ou non, et pour ce faire, il nous est possible de faire des comparaisons entre la valeur d'une colonne (ou le résultat d'une requête) et le résultat d'un groupement.

À partir de notre jeu de données, nous allons faire un test. Nous savons que nous avons pu facturer nos prestataires à différents clients au cours du mois. Cependant, est-ce que ces personnes nous rapportent suffisamment par rapport à leur salaire, ou serait-il nécessaire de leur trouver plus de missions. Ou bien faut-il facturer plus les clients ?

Nous connaissons le salaire des employés, et nous venons de calculer la valeur de leurs différentes interventions.

Nous allons utiliser la fonction HAVING. Elle doit nous permettre de comparer le résultat d'un groupement à une valeur fixe.

Pour ce faire, reprenons notre précédente requête et ajoutons la clause.

SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE, SALAIRE_EMPLOYE, MONTH( DATE_INTERVENTION ) 
 HAVING SUM( FACTURATION ) > SALAIRE_EMPLOYE;

Vous pourrez constater que le groupement devient quelque peu différent. En effet, l'ajout du SALAIRE_EMPLOYE dans le groupement devient obligatoire si l'on souhaite le comparer. Pourquoi ?

Comme expliqué plus haut, toute colonne utilisée dans la requête et n'étant pas soumise à un agrégat doit obligatoirement se trouver dans le groupement.

J'ai besoin de comparer le SALAIRE à la somme des facturations. Il me faut donc définir le SALAIRE comme une donnée non agrégée.

Le résultat est sans appel ! Il manque John à l'appel des personnes « rentables ». Qu'à cela ne tienne, on facturera de la même façon que les autres.

Maintenant que l'on a vu les fonctionnalités de base des groupements, nous allons voir quelques fonctionnalités avancées qui devraient vous faciliter la vie pour les rapports.

Nous l'avons vu, GROUP BY est une fonction assez simple qui agrège les données sur des critères spécifiques, mais ne donne pas de détails ni ne fait de sommes particulières.

Nous allons compléter notre jeu de données avec des interventions sur un nouveau mois.

INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 36, '2014-02-03', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 37, '2014-02-04', 'Client 1', 2, 500 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 38, '2014-02-05', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 39, '2014-02-06', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 40, '2014-02-07', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 41, '2014-02-10', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 42, '2014-02-11', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 43, '2014-02-12', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 44, '2014-02-13', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 45, '2014-02-14', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 46, '2014-02-03', 'Client 1', 4, 400 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 47, '2014-02-04', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 48, '2014-02-05', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 49, '2014-02-06', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 50, '2014-02-07', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 51, '2014-02-03', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 52, '2014-02-04', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 53, '2014-02-05', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 54, '2014-02-06', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 55, '2014-02-07', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 56, '2014-02-10', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 57, '2014-02-19', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 58, '2014-02-06', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 59, '2014-02-07', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 60, '2014-02-10', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 61, '2014-02-11', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 62, '2014-02-12', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 63, '2014-02-06', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 64, '2014-02-07', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 65, '2014-02-10', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 66, '2014-02-11', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 67, '2014-02-12', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 68, '2014-02-03', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 69, '2014-02-06', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 70, '2014-02-07', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 71, '2014-02-10', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 72, '2014-02-11', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 73, '2014-02-12', 'Client 4', 9, 750 );

Nous voici donc avec un jeu de données pour janvier et février. Si l'on refait la requête précédente, nous aurons de nouvelles lignes dans le tableau.

Nous observons bien les deux mois et les noms des employés associés à chaque mois d'intervention.

Le GROUP BY est, sans conteste, quelque chose de courant dans les bases de données. Mais il a ses limites. La question, au-dessus, va nous poser un problème.

La réponse est : « Impossible en une requête avec un simple GROUP BY ». Pour réaliser la demande, il nous faut trois requêtes que l'on va unir avec la clause UNION.

La première, on l'a vu au-dessus, calcule au mois. La seconde (ci-dessous) calcule à l'employé.

SELECT NOM_EMPLOYE 
     , 'Total'            AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION ) AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE;

Cette requête calcule le total des interventions par utilisateur et devrait être unie à la précédente afin de restituer en un seul jeu de données le total par utilisateur.

Enfin, une troisième requête est nécessaire pour calculer le total global des interventions.

SELECT 'Total'            AS NOM_EMPLOYE 
     , NULL               AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION ) AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID;

Ce tableau représente l'union des trois requêtes :

En faisant un tri sur le nom puis le mois d'intervention, il est possible d'obtenir un tableau plus cohérent. Cependant, nous pouvons le constater, dans l'ordre alphabétique, « Virginie » est classée après « Total ». Pour contourner ce problème, il nous faudrait rajouter une colonne avec un chiffre défini ce qui rajouterait encore du code !

Maintenant le ROLLUP !

Comment cela fonctionne-t-il ? Eh bien comme un GROUP BY, nous citons une série de colonnes à regrouper.

Dans notre cas, toujours le mois et le consultant.

SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY ROLLUP (NOM_EMPLOYE, MONTH( DATE_INTERVENTION ));

Vous constaterez la forme de l'écriture. Nous sommes toujours dans un GROUP BY à ceci près que nous précisons la fonctionnalité ROLLUP et que nous plaçons entre () les colonnes du groupement. Pourquoi ? Simplement parce que cette fonction nous permet, en une requête, d'afficher plusieurs groupements différents, tous séparés par une « , ». D'où le besoin des parenthèses pour permettre au SGBD de ne pas s'emmêler les pinceaux.

Le résultat est immédiat (ou presque, tout dépend des volumes et de l'optimisation de votre base !)

Le SGBD a déjà calculé les sous-niveaux. Un total mensuel, un total par employé et un total !

Eh bien, pour chaque colonne du groupement en partant de la droite, il va calculer les regroupements à tous les niveaux. Dans notre cas, il calcule le groupement par PERSONNE/ANNEE puis, simplement par PERSONNE et pour finir sans groupement.

Il rassemble nos trois requêtes précédentes pour lesquelles j'ai dû faire une manipulation pour supprimer les colonnes en trop dans les groupements.

Comme vous pouvez le constater, les regroupements supérieurs n'ont pas de valeur. Il nous suffira de mettre des fonctions ISNULL afin de placer les valeurs que l'on souhaite.

SELECT ISNULL( NOM_EMPLOYE, 'Total Annuel' ) AS NOM_EMPLOYE 
     , CASE WHEN NOM_EMPLOYE IS NULL AND MONTH( DATE_INTERVENTION ) IS NULL 
            THEN NULL 
            ELSE ISNULL( CAST( MONTH( DATE_INTERVENTION ) AS VARCHAR ), 'Total Mensuel' ) END AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY ROLLUP (NOM_EMPLOYE, MONTH( DATE_INTERVENTION ));

Et voilà, comment en une requête on peut ressortir un tableau complet et juste !

Nous tenons à remercier zoom61 pour la mise au gabarit et f-leb pour la relecture orthographique.