0

I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses:

"ORA-00907: missing right parenthesis"

How could I convert this into Oracle accepted script?

SELECT pdate, 
       COUNT(status) total, 
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded, 
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;
3

That is invalid SQL for Oracle - you can't apply sum on a boolean expression (it works on MySQL because it treats boolean expressions as numbers). You need to use a CASE for that:

SELECT pdate, 
       COUNT(status) total, 
       count(case when status='successful' then 1 end) successful,
       count(case when status='failed' then 1 end) failed,
       count(case when status='refunded' then 1 end) refunded, 
       count(case when status='successful' then 1 end)/COUNT(status)*100 successful_percent,
       count(case when status='failed' then 1 end)/COUNT(status)*100 failed_percent,
       count(case when status='refunded' then 1 end)/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;

This works because count() (like every other aggregate) ignores NULL values. The expression case when status='successful' then 1 end yields null for any status other than 'successful' and a not-null value or those that are successful, effectively counting only those that have the indicated status.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.