SQL group by ranges and keep 0

Question

I justed read this page: In SQL, how can you "group by" in ranges?

The response helped me but I have a question, If the count() for a range equals 0, the range will not showed, how can I show the range when the count() equals to 0 ?

Here is my query:

select 
    w.Période, 
    nb from( select t.Période, count(*) as nb 
from (select 
          *, 
          case 
              when report.creation_date between '2017-05-28 00:00:00' and '2017-06-25 00:00:00' 
                  then 'P3' 
              when report.creation_date between '2017-06-25 00:00:00' and '2017-07-23 00:00:00' 
                  then 'P4' 
              when report.creation_date between '2017-07-23 00:00:00' and '2017-08-20 00:00:00' 
                  then 'P5' 
              when report.creation_date between '2017-08-20 00:00:00' and '2017-09-17 00:00:00' 
                  then 'P6' 
              else 'Avant' 
          end as Période 
      from report 
      where report.office_id = 11) as t 
      group by t.Période ) as w

Here is my result:

Avant 57
P3 1
P5 2
P6 4

I would like to have:

Avant 57
P3 1
P4 0
P5 2
P6 4

I have tried to use these but it doesn't work. case when count() is null then 0 else count() end as nb or case when count() = 0 then 0 else count() end as nb or case when count() REGEXP '[^[:digit:]]' then 0 else count() end as nb

Answer 1

You should join them against a derived table:

SELECT t.period,
       COALESCE(s.nb,0) as yourCount
FROM(SELECT 'P1' as period 
     UNION ALL 
     SELECT 'P2' 
     UNION ALL 
     ...) t -- This is the derived table
LEFT JOIN(Your Query Here) s -- This is your query
 ON(t.period = s.period)

Answer 2

To slightly cleanup from what was offered by sagi, you have extra bloat and grouping, but will need a pre-query for all the qualified periods you want in your output... This will be your primary table being queried. Then, a simple select from/group by is the LEFT-JOIN

select 
     preQuery.Période, 
     coalesce( PreSum.AllRecs, 0 ) as TotalRecs
   from
      ( select 'P3' as Période
        union all select 'P4'
        union all select 'P5'
        union all select 'P6'
        union all select 'Avant' ) preQuery
         LEFT JOIN
         ( select 
                 case when r.creation_date between '2017-05-28 00:00:00' 
                                               and '2017-06-25 00:00:00' 
                      then 'P3'
                      when r.creation_date between '2017-06-25 00:00:00'
                                               and '2017-07-23 00:00:00' 
                      then 'P4' 
                      when r.creation_date between '2017-07-23 00:00:00'
                                               and '2017-08-20 00:00:00' 
                      then 'P5' 
                      when r.creation_date between '2017-08-20 00:00:00'
                                               and '2017-09-17 00:00:00' 
                      then 'P6' 
                      else 'Avant' 
                 end as Période,
                 count(*) as AllRecs
            from 
               report r
            where 
               r.office_id = 11
            group by
                 case when r.creation_date between '2017-05-28 00:00:00' 
                                               and '2017-06-25 00:00:00' 
                      then 'P3'
                      when r.creation_date between '2017-06-25 00:00:00'
                                               and '2017-07-23 00:00:00' 
                      then 'P4' 
                      when r.creation_date between '2017-07-23 00:00:00'
                                               and '2017-08-20 00:00:00' 
                      then 'P5' 
                      when r.creation_date between '2017-08-20 00:00:00'
                                               and '2017-09-17 00:00:00' 
                      then 'P6' 
                      else 'Avant' end ) PreSum
             on PreQuery.Période = PreSum.Période

Answer 3

If you have the values in the table, but just not for that office, you can do:

  select (case when r.creation_date between '2017-05-28' and '2017-06-25' 
               then 'P3' 
               when r.creation_date between '2017-06-25' and '2017-07-23' 
               then 'P4' 
               when r.creation_date between '2017-07-23' and '2017-08-20' 
               then 'P5' 
               when r.creation_date between '2017-08-20' and '2017-09-17' 
               then 'P6' 
               else 'Avant' 
           end) as Période ,
          sum(r.office_id = 11) as cnt
  from report r
  group by Période;

The conditional aggregation method is often simpler to implement than using an outer join. The outer join is more general in the sense that you explicit select the rows that you want. This method is more general in the sense that any period in the data will be represented -- even if it is a new one.

Some notes:

• There is no need for so many subqueries. That just makes the work of the optimizer harder to do and more difficult for humans to follow the query.
• I'm a big fan of qualified column names (the r.s in the query).
• You don't need to include 00:00:00 when specifying a date.

In fact, clearer logic would be:

If you have the values in the table, but just not for that office, you can do:

  select (case when r.creation_date < '2017-06-26' 
               then 'P3' 
               when r.creation_date < '2017-07-24' 
               then 'P4' 
               when r.creation_date < '2017-08-21' 
               then 'P5' 
               when r.creation_date < '2017-09-18' 
               then 'P6' 
               else 'Avant' 
           end) as Période ,
          sum(r.office_id = 11) as cnt
  from report r
  where r.creation_date >= '2017-05-28'
  group by Période;

Note that the inequality < is used. This is handy because it works consistently for both dates and date/times.