CODEWITHSUNDEEP
mysqlsql
Alexy Durand
Alexy Durand

Reputation: 31

MYSQL : Group by all weeks of a year with 0 included

I have a question about some mysql code.

I have a table referencing some employees with the date of arrival et the project id. I wanna calculate all the entries in the enterprise and group it by week.

A this moment, I can have this result

Project ID | Week | Count
1 | 2019-S01 | 2
1 | 2019-S03 | 1
2 | 2019-S01 | 1
2 | 2019-S04 | 5
2 | 2019-S05 | 3
2 | 2019-S06 | 2

This is good, but I would like to have all the weeks returned, even if a week has 0 as result :

Project ID | Week | Count
1 | 2019-S01 | 2
1 | 2019-S02 | 0
1 | 2019-S03 | 1
...
2 | 2019-S01 | 1
2 | 2019-S02 | 0
2 | 2019-S03 | 0
2 | 2019-S04 | 5
2 | 2019-S05 | 3
2 | 2019-S06 | 2
...

Here is my actual code :

SELECT
        AP.SECTION_ANALYTIQUE AS SECTION,
        FS_GET_FORMAT_SEMAINE(AP.DATE_ARRIVEE_PROJET) AS SEMAINE,
        Count(*) AS COMPTE
FROM
        RT00_AFFECTATIONS_PREV AP
WHERE 
        (AP.DATE_ARRIVEE_PROJET <= CURDATE() AND Year(AP.DATE_ARRIVEE_PROJET) >= Year(CURDATE())) 
GROUP BY 
        SECTION, SEMAINE
ORDER BY 
        SECTION

Does anybody have a solution ?

I searched things on internet but didn't find anything accurate :(

Thank you in advance ! :)

Upvotes: 2

Views: 54

Answers (1)

GMB
GMB

Reputation: 222622

The classic way to meet this requirement is to create a referential table to store all possible weeks.

create table all_weeks(week varchar(8) primary key);
insert into all_weeks values
    ('2019-S01'), ('2019-S02'), ('2019-S03'), ('2019-S04'), ('2019-S05'), ('2019-S06');

Once this is done, you can generate a cartesian product of all possible sections and weeks with a CROSS JOIN, and LEFT JOIN that with the original table.

Given your code snippet, this should look like:

 SELECT
    s.section_analytique AS section,
    w.week AS semaine,
    COUNT(ap.section_analytique) AS compte
FROM
    (SELECT DISTINCT section_analytique from rt00_affectations_prev) s
    CROSS JOIN all_weeks w
    LEFT JOIN rt00_affectations_prev ap
        ON s.section_analytique = ap.section_analytique AND w.week = FS_GET_FORMAT_SEMAINE(ap.date_arrivee_projet)
GROUP BY s.section_analytique, w.week
ORDER BY s.section_analytique

PS: be careful not to put conditions on the original table in the WHERE clause: this would defeat the purpose of the LEFT JOIN. If you need to do some filtering, use the referential table instead (you might need to add a few columns to it, like the starting date of the week maybe).

Upvotes: 1

Related Questions