CODEWITHSUNDEEP
sqloracle-database
Corey Simpson
Corey Simpson

Reputation: 1

Oracle SQL Column division error

I have this piece of Oracle SQL that has been giving me problems on and off. I have got it to work one time today and then the next time I tried to run it I was given an SQL command not properly ended. All I'm trying to do is divide one column by itself under the 2 different restraints. 

SELECT start_end_amt,
    in_out_amt
FROM MYTABLE.MYTABLE,
    (
        SELECT sum(start_end_amt)
        FROM MYTABLE.MYTABLE
        WHERE in_out_amt = 'C'
        ) / (
        SELECT sum(start_end_amt)
        FROM MYTABLE.MYTABLE
        WHERE in_out_amt = 'D'
        ) * 100 AS RATIO
ORDER BY RATIO,
    start_end_amt,
    in_out_amt

However, now that I run it this way I am getting an error on the *100 part. It's an SQL command not properly ended. As far as I was aware this was the proper way to write a select query.

My expected outcome is to have a table that returns a percent ratio for each transaction that occurs. The one time it did work it was not outputting a percent either unfortunately.

Upvotes: 0

Views: 195

Answers (1)

Patrick H
Patrick H

Reputation: 653

I think you just simply need to group by a transaction ID and do conditional aggregation. Something like this:

select transaction_id,
       sum(case when in_out_amt = 'C' then start_end_amt else 0 end) /
          sum(case when in_out_amt = 'D' then start_end_amt else 0 end) * 100 RATIO
  from MYTABLE.MYTABLE
group by transaction_id

Upvotes: 2

Related Questions