I have a table with the following data: table values

When I use the below query in PostgreSQL, it works as expected

select 
  case when total = 0 or null                                 then 'skip'
       when round((score / total * 100) ,2) >= 91             then '>=91'
       when round((score / total * 100) ,2) between 80 and 90 then '>=80 & <=90'
       else                                                        'other' 
  end as perc,
  count(*)
from public.customer  
group by perc;

Results

The same syntax does not work in db2. I've tried all kinds of variations with no luck.

Any assistance would be much appreciated. Thank you.

CodePudding user response：

I think you intended to check if total be NULL inside your CASE expression. If so, then you should be using IS NULL to do the check:

select 
    case when total = 0 or total is null                        then 'skip'
         when round((score / total * 100) ,2) >= 91             then '>=91'
         when round((score / total * 100) ,2) between 80 and 90 then '>=80 & <=90'
         else                                                        'other' 
    end as perc,
    count(*)
from public.customer  
group by perc;

CodePudding user response：

Db2 supports Grouping by SELECT clause columns in so called "Netezza compatibility mode" only, which you may turn on setting the SQL_COMPAT global variable in your session.
You must use subselect (commented out) otherwise.

SET SQL_COMPAT = 'NPS';

WITH CUSTOMER (SCORE, TOTAL) AS
(
  VALUES
    (92.5, 100.0)
  , (92.5, 100.0)
  , (85.0, 100.0)
  , (83.0, 100.0)
  , (92.5, 100.0)
)
/*
SELECT PERC, COUNT (*) AS COUNT
FROM
(
  SELECT
  case 
    when coalesce (total, 0) = 0
      then 'skip'
    when round((score / total * 100) ,2) >= 91
       then '>=91'
    when round((score / total * 100) ,2) between 80 and 90
       then '>=80 & <=90'
    else 'other' 
  end as perc
  FROM CUSTOMER 
)
GROUP BY PERC
*/
SELECT
  case 
    when coalesce (total, 0) = 0
      then 'skip'
    when round((score / total * 100) ,2) >= 91
       then '>=91'
    when round((score / total * 100) ,2) between 80 and 90
       then '>=80 & <=90'
    else 'other' 
  end as perc
, COUNT (*) AS COUNT
FROM CUSTOMER 
GROUP BY PERC;