Rheine Rheine - 6 months ago 16
SQL Question

Calculating percentage from two SQL Count() results

I need to count the percentage of finalized transactions compared to total transactions (e.g. including in-process and finalized transactions). From looking around the web, I arrived at:

SELECT 100 * (SELECT COUNT(transaction_id) from t_transaction_main
WHERE due_date = '2016-05-16' and (suspend_status !='' OR
close_date != '0-0000-00'))/COUNT(transaction_id) from t_transaction_main as test;


And it worked thus far; however, while the finalized transaction query is as intended, I actually need to divide it by the transaction only on a particular date (here, 2016-05-16). But when I added a WHERE clause...

SELECT 100 * (SELECT COUNT(transaction_id) from t_transaction_main
WHERE due_date = '2016-05-16' and (suspend_status !='' OR
close_date !='0-0000-00'))/COUNT(transaction_id) WHERE due_date = '2016-05-16' from t_transaction_main as test;


It immediately stopped working and returned a syntax error. I've also tried wrapping the
COUNT(transaction_id) WHERE due_date = '2016-05-16'
inside parentheses, but it didn't help.

Any solution would be welcomed, thank you.

Answer

Wrong clause sequence you put the where before the from clause

SELECT 100 * (SELECT COUNT(transaction_id) 
        from t_transaction_main   
        WHERE due_date = '2016-05-16' and (suspend_status !='' OR
  close_date !='0-0000-00'))/COUNT(transaction_id) 
FROM t_transaction_main as test 
WHERE due_date = '2016-05-16' ;
Comments