mismatched input 'as'. Expecting: ',', <expression>-CodePudding

My query in PRESTO returns this error

Query failed (#20220506_153121_03035_vycq3): line 6:41: mismatched input 'as'. Expecting: ',',

I don't know why, anybody could find the issue?

select 
    droh.created_by as deliverymen_email,
    count(distinct o.order_number) as deliveries,
    sum(case when o.last_status = 10 then 1 else 0 end) quantity_canceled,
    cast(sum(quantity_canceled as decimal))/cast(count(deliveries as decimal)) as 
    delivery_cancellation_fee
from sensitive_raw_courier_api.deliveryman_route_order_history droh
    left join raw_courier_api."order" o
         on droh.order_number = o.order_number and droh.state = 'DM_PICKED_UP'
where 1=1
    and o.created_date >= {{date_begin}}
    and droh.created_at >= {{date_end}}
    and o.customer_email = {{costumer_email}}
group by 1
order by 2 desc

CodePudding user response：

There is an error with the position of 2 brackets.
We count(~) first and then cast( count(~) ) the result.

cast(sum(quantity_canceled as decimal))/cast(count(deliveries as decimal)) as

should be

cast(sum(quantity_canceled) as decimal)/cast(count(deliveries) as decimal) as

CodePudding user response：

Without the context and further information, it's not sure if this is your only issue in this query, but you can't "mix" a cast with a sum or a count like you do. You need to do the cast first and then sum or count the values (or vice versa). So as example this syntax in your query is incorrect:

CAST(SUM(quantity_canceled AS DECIMAL))

It should be this one instead:

SUM(CAST(quantity_canceled AS DECIMAL))

Or this one:

CAST(SUM(quantity_canceled) AS DECIMAL)

You must fix all occurences of this mistake and then check if the query is correct or contains further problems. A last note: Doing a division could always end in a division by zero exception if you don't prevent this. So you should take care of this.