CODEWITHSUNDEEP
sqloracle-database
user1444329
user1444329

Reputation: 37

Oracle SQL - Show percentage of two columns in new column

I have a query that is showing the volume per week of Pass / Fails for a test as two separate columns. I want to add a third column, that shows the percentage of fails - (1/(Failed+Passed))*Failed.

SELECT to_char(DATE,'YYYY'), to_char(DATE,'WW'), 
  SUM(case WHEN Result = 'Y' then 1 else 0 end) Failed, 
  SUM(case WHEN Result = 'N' then 1 else 0 end) Passed
FROM Result_table
GROUP BY to_char(DATE,'YYYY'), to_char(DATE,'WW')
ORDER BY to_char(DATE,'YYYY'), to_char(DATE,'WW');

I believe I have to use the above as a sub query (so that Failed and Passed are already defined), however, I keep getting errors - so I am not sure if that is the right approach.

Any help would be much appreciated.

Cheers

Upvotes: 0

Views: 3021

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

To use this as a subquery you need to move the order by to the outer query, which means you need the year and week columns so be aliased:

SELECT year_num, week_num, failed, passed,
  100 * failed / (failed + passed) as failed_percent
FROM (
  SELECT to_char(DATE,'YYYY') as year_num,
    to_char(DATE,'WW') as week_num, 
    SUM(case WHEN Result = 'Y' then 1 else 0 end) as failed, 
    SUM(case WHEN Result = 'N' then 1 else 0 end) as passed
  FROM Result_table
  GROUP BY to_char(DATE,'YYYY'), to_char(DATE,'WW')
)
ORDER BY year_num, week_num;

Upvotes: 1

Related Questions