Question 89309 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – grouping error with Case and When to avoid division by zero

ERROR: column "sams_ar_pb_rebuild.plan_units_ty" must appear in the
GROUP BY clause or be used in an aggregate function

select
    channel,
    category,
    club,
    week_ending,
    sum(eligible_units_ty) as "Eligible Units",
    sum(eligible_revenue_ty)::numeric as "Eligible Revenue",
    sum(plan_units_ty) as "Plan Units",
    sum(plan_revenue_ty)::numeric::money as "Plan Revnue",
   case when plan_units_ty =0 then null
   else round(100*(sum(plan_units_ty)/sum(eligible_units_ty)),1)end as "AR"
from operations.sams_ar_pb_rebuild 
    where week_ending >= '2023-01-01'and category not in ('Mattresses','Monthly Mobile')
 group by 1,2,3,4
order by 1 desc, 2 desc, 3 desc, 4 desc

WHen not using case then it will resulted in division by zero error code

Tags:

2

Answers


  1. 0

    Every non-aggregate column must appear in the group by clause. Originally the column was presumably just the sums, but case when plan_units_ty =0 then null is not aggregated. And it won’t solve your problem.

    Looking at sum(plan_units_ty)/sum(eligible_units_ty), the divide-by-zero error is when sum(eligible_units_ty) is 0. You need to account for that.

       case when sum(eligible_units_ty) = 0 then 'NaN'
   else round(100*(sum(plan_units_ty)/sum(eligible_units_ty)),1)end as "AR"

    All branches of the case must return the same type, so it has to be a number. I chose NaN (Not A Number).

    Demonstration.

    Login or Signup to reply.
  2. 0

    try using nullif the value is 0

    round(100*(sum(plan_units_ty)/Nullif(sum(eligible_units_ty),0)),1)end as "AR"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search