Percentage of count in Oracle

aggregateoracle

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;

Best Answer

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.

Related Question