Skip to content

Learn how to build data aggregations in a BI database, a tutorial from Lyche

Totals and subtotals function

Presentation

Business Intelligence databases are confronted with recurring problems of calculations, statistics, or various data aggregations. Many imagine that summing up and other grade averages is just applying simple mathematical formulas. However, when it comes to databases, these calculations take on another dimension, the complexity of which depends on management rules and customer needs.

Aggregations

Aggregations, despite the scary name, are simple mathematical calculations. The table below presents the most common aggregation functions.

Function Serves
AVG() Calculate the average of the aggregated values
COUNT() Calculate the number of rows
MAX() Get the maximum value of a column
MIN() Get the minimum value of a column
SUM() Calculate sum of column values

Practical case

1
2
3
4
5
6
7
8
9
10
11
12
13

;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

This example uses a fairly simple dataset: a list of people with their ages, on which the various calculations required are made. First, the sum of the ages, then the average age, then the youngest, the oldest, and finally the number of rows in our dataset.
GROUP BY

These features are not difficult to understand. What can complicate the task is the difficulty of associating a certain number of columns with specific calculations. What we will call the grouping of data, (GROUP BY in SQL language)
This feature of the SQL standard makes it possible to define common elements for grouping information.
Before going into the somewhat theoretical text, which is not always clear and sometimes difficult to understand, a small practical case.
Practical case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

  
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 );

The above commands create 2 employee/department tables linked by department_id.
We are here within the framework of a company which will want to make some statistics on its employees.
1 Sum the salaries by department.

1
2
3
4
5

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

You will notice the use of the GROUP BY function, associated with the NAME_DEPARTMENT column.
You will notice that the value of the column TOTAL_SALAIRE is equal to the total sum of the salaries entered for the list of employees.
Explanation

At the DBMS level, the command instructs him to sum the salary column and to do this calculation by department name (therefore, to group identical information in a single line).
So we end up with our 4 departments and their respective monthly budgets.
We could do exactly the same calculations as in the first part of our post.
2 Calculate the average salary, the minimum and maximum salary, as well as the number of employees per department

1
2
3
4
5
6
7
8
9

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

Here, by studying, we see some discrepancies, especially at the level of the IT department where there are several employees.

  • The query is used here to calculate several types of information based on DEPARTMENT_NAME as a common criterion.

Important :
It often happened to me to have requests concerning false requests because of a bad management of the GROUP BY. To make it as simple as possible in terms of its practice, you should know that any column found in a select and not belonging to an aggregation function, must be found in the GROUP BY clause.
What is most difficult is identifying the fields of the select outside the aggregate. It is sometimes necessary to return to the question asked (or the GR) in order to be as precise as possible on the fields.

  • note that it is possible to aggregate data on the result of a function.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

  
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 );

Here we are creating a set of intervention lines for our employees with customers. We would like to have information about the months of work.

What is the sum of the billings of our employees for the past months, without taking into account the place of intervention?

1
2
3
4
5
6

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 );

We took into account 3 elements of the request.

  • Month of Intervention
  • Collaborater
  • Sum of invoices.
  • These 3 pieces of information make it possible to identify the necessary grouping as well as the desired aggregation.

We end up with the MONTH() function, which allows you to highlight the month of a date, in the select. Contrary to ORDER BY which can work with the position of the field in the select, GROUP BY requires to reproduce exactly the same commands as during the SELECT.

  • To facilitate the writing of a GROUP BY, a CTE, a Drive table or the use of a view, are good solutions.

The HAVING clause

Now that we know how to group data in order to aggregate it, we are going to see how to exploit the result of our grouping and how to limit the results to our expectations.
As we saw above, it is possible to calculate the sums of interventions of a person, now, imagine that the consultants sent on missions have a cost for the company. This cost is the salary.
From these two pieces of information, the salary and the interventions, we can deduce which consultant is profitable or not, and to do this, we can make comparisons between the value of a field (or the result of a query)

From our dataset, we are going to do a test.

  • We know that we were able to invoice our different service providers during the month. However, do these people bring us enough in relation to their salary, or would it be necessary to find more assignments for them. Or should customers be charged more?

We know the salary of the employees, and we have just calculated the value of their various interventions.
However, what about what they report the company?

We are going to use the HAVING function. It should allow us to compare the result of a grouping to a fixed value.

To do this, let's go back to our previous query and add the clause.

1
2
3
4
5
6
7

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;

You may find that the grouping becomes somewhat different. Indeed, the addition of the SALAIRE_EMPLOYE in the grouping becomes compulsory if one wishes to compare it. Why ?
As explained above, any column used in the query that is not submitted to an aggregate must be in the grouping.
I need to compare the SALARY the sum of the billings. So I have to define the SALARY as a non-aggregated datum.

The result is final! John lacks the appeal of profitable people. Never mind, we will charge the same way as the others

Now that we've covered the basic features of groupings, let's look at some advanced features that should make your reporting life easier.

GROUP BY ROLLUP

As we have seen, GROUP BY is a fairly simple function that aggregates data by specific criteria but does not give details or do particular sums.

We are going to complete our dataset with interventions for a new month.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

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 );

So here we are with a dataset for January and February. If we redo the previous query, we will have new rows in the table.

We carefully observe the 2 months and the names of the employees associated with each month of intervention.

This total is not precise enough! I need the annual calculation per employee AND an annual company total!

GROUP BY is, without a doubt, something common in databases. But it has its limits. The question above will pose a problem for us.
How can we aggregate different levels in the same query?

The answer is Impossible in a query with a simple GROUP BY. To carry out the request, we need 3 requests that we will join with the UNION clause.

The first, as seen above, is calculated on a monthly basis. The second (below) calculates the employee.

1
2
3
4
5
6

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;

This query calculates the total number of interventions per user and should be joined to the previous one in order to return the total per user in a single dataset.
Finally, a third query is necessary to calculate the global total of the interventions.

1
2
3
4
5

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;

This table represents the union of the 3 queries

By sorting on the name then the month of intervention, it is possible to obtain a more coherent table. However, we can see that Virginie is in alphabetical order and is after the total. To work around this problem, we would have to add a column with a defined number, which would add more code!

Now The ROLLUP!

How does it work ? Well like a GROUP BY we quote a series of columns to group.
In our case, always the month and the consultant.

1
2
3
4
5
6

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 ));

You will see the shape of the writing. We are still in a GROUP BY except that we specify the ROLLUP functionality and place the grouping columns between (). Why ? Simply because this function allows us, in one query, to display several different groupings, all separated by a , . Do the need for the parentheses to allow the DBMS not to merge the brushes.

The result is immediate (or almost everything depends on the volumes and the optimization of your base!)

The DBMS has already calculated the sub-levels. A monthly total, a total per employee and a total!

What exactly does ROLLUP do?

Well, for each column of the grouping starting from the right it will calculate the groupings at all levels. In our case, it calculates the grouping by PERSON/YEAR then, simply by PERSON and finally without grouping.
It brings together our 3 previous queries for which I had to do something to remove the extra columns from the groupings.

As you can see, the higher groupings have no value. We just need to put ISNULL functions in order to place the values ​​we want.

1
2
3
4
5
6
7
8

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 ));

And that's how, in a query, you can get a complete and accurate table!

Leave a Reply

Your email address will not be published.