I have a case statement that put multiple conditions into one field.

Select 
    *,
    case 
        when day <= 30 then 'Expiration < 30 days'
        when day between 31 and 60 then 'Expiration between 31 and 60 days'
        when day between 61 and 90 then 'Expiration between 61 and 90 days'
        when day > 90 then 'Expiration > 90 days' 
    end Expiration_Version
from 
    tb1;

I need to calculate count for each condition here after case condition is specified, but i run into a problem is that if there is no result return for a particular condition, then case when statement would not include that condition. I'd like to have all conditions return even if there is no result. The count will be 0 in that case.

Current state: if there is one condition not met, like if there is no expiration less than 30 days, 'Expiration < 30 days' won't show up in the aggregation.

Expiration Version                           Count
Expiration between 31 and 60 day              xx
Expiration between 61 and 90 days             xx
Expiration > 90 days                          xx

what I prefer:

Expiration Version                           Count
Expiration < 30 days                          0
Expiration between 31 and 60 day              xx
Expiration between 61 and 90 days             xx
Expiration > 90 days                          xx

CodePudding user response：

Like @nir-h said in his answer, you can make a LEFT JOIN between "expiration lavels" values and your table:

I set the first interval from 0 to 30 (including 30).

Last interval finish column has to have a value which is higher than the max amount of days, to not exclude any row of your table.

Postgresql (and SQL Server):

I don't know which RDBMS are you using. This query work on Postgresql, but you can adapt it so that it works in another RDBMS.

fiddle

SELECT e.expiration_lavel AS Expiration_Version , count(tb1.day) AS "Count"

FROM (VALUES
      (1, 0, 30, 'Expiration <= 30 days'),
      (2, 31, 60, 'Expiration between 31 and 60 days'),
      (3, 61, 90, 'Expiration between 61 and 90 days'),
      (4, 91, 1000, 'Expiration > 90 days')) e (id, start, finish, expiration_lavel)
      
LEFT JOIN tb1 ON tb1.day BETWEEN e.start AND e.finish

GROUP BY e.id, e.expiration_lavel

ORDER BY e.id;

Oracle (and MySql):

SELECT e.lavel Expiration_Version, count(tb1.day) Count

FROM (SELECT 1 id, 0 f, 30 t, 'Expiration <= 30 days' lavel
      FROM dual

      UNION ALL

      SELECT 2, 31, 60, 'Expiration between 31 and 60 days'
      FROM dual

      UNION ALL

      SELECT 3, 61, 90, 'Expiration between 61 and 90 days' 
      FROM dual

      UNION ALL

      SELECT 4, 91, 1000, 'Expiration > 90 days'
      FROM dual) e
      
LEFT JOIN tb1 ON tb1.day BETWEEN e.f AND e.t

GROUP BY e.id, e.lavel

ORDER BY e.id; 

Output with my sample data:

CodePudding user response：

You can create a table with the four possible Expiration_Version values, and then perform a left join between that table and the table that counts the occurrences of each possible value. This way you will get Null for values with no occurrences.