Oracle - Case Counting on math

I'm trying to do some case counting on the fly and was hoping someone could help me out here. Any idea how to make this work?

What I want to do is perform some math on columns and then count the number of records that meet the criteria. So for example I have this data

REPORT  bad_count   good_count
------------------------------
Y       30          20
Y       1           100

I would want to see the count of records where the bad_count is >= 20% of the total count... (bad+good) like this

REPORT  stuff
-------------
Y       1

Here's the query I had in mind, but I receive an error.

select      REPORT,
            count(case round(bad_count / (good_count + bad_count) * 100) when >=20 then 1) as stuff
from        $A$
group by    REPORT

---

This suggested answer from below worked

SELECT REPORT, COUNT(*) 
  FROM (SELECT REPORT, ROUND((bad_ct/(good_ct+bad_ct))*100) pct
         FROM $A$)
 WHERE pct >= 20
 GROUP BY REPORT;

but, why does it not work when re-written like this?

 SELECT         REPORT,
                count(case pct when >=20 then 1 end) as stuff
    FROM        (
                    SELECT  REPORT, 
                            ROUND((bad_ct/(good_ct+bad_ct))*100) pct
                    FROM    $A$
                )
    GROUP BY    REPORT

The reason I prefer to do it this way is I may want to count instance where there are other criteria as well. For example I also want a new开发者_如何学JAVA column 'good_stuff' which is a count of how many records also had good_ct that isn't null.

---

Something like this:

SELECT REPORT, COUNT(*) 
  FROM (SELECT REPORT, ROUND((bad_ct/(good_ct+bad_ct))*100) pct
         FROM $A$)
 WHERE pct >= 20
 GROUP BY REPORT;

EDIT:

My interpretation of the question was a bit different than the other responders. I took the question to be "what is the count of rows (grouped by the REPORT field) where the bad count for the row is >= to the total count for the row."

Testing gives:

SQL> CREATE TABLE TEST (REPORT VARCHAR2(10), bad_count INTEGER, good_count INTEGER);

Table created
SQL> INSERT INTO TEST VALUES('Y',30,20);

1 row inserted
SQL> INSERT INTO TEST VALUES('Y',1,100);

1 row inserted
SQL> INSERT INTO TEST VALUES('Y',20,80);

1 row inserted
SQL> INSERT INTO TEST VALUES('Y',19,80);

1 row inserted

SQL> commit;

Commit complete

SQL> 
SQL> SELECT REPORT, COUNT(*) FROM (
  2  SELECT REPORT, ROUND((bad_count/(good_count+bad_count))*100) pct
  3    FROM TEST)
  4   WHERE pct >= 20
  5   GROUP BY REPORT;

REPORT       COUNT(*)
---------- ----------
Y                   2

SQL> 

---

I believe you're looking for

select      report,
            (case when round( total_bad/ (total_good + total_bad) * 100) >= 20
                  then 1
                  else 0
              end) stuff
from (
    select      REPORT,
                SUM(bad_count) total_bad,
                SUM(good_count) total_good
    from        $A$
    group by    REPORT
)

You should be able to do it without the subquery by putting the aggregates in the CASE statement but this formulation strikes me as easier to follow.

---

Take advantage of Oracle's analytics functions:

SELECT REPORT, 1 stuff
FROM (
   SELECT REPORT,
          sum(good_count) over (partition by REPORT) total_good,
          sum(bad_count) over (partition by REPORT) total_bad
   FROM REPORT 
) WHERE round( total_bad / (total_good + total_bad) * 100) >= 20
ORDER BY REPORT;

---

You are trying to mix the two distinct syntaxes of CASE.

In one syntax, the CASE keyword is immediately followed by the first WHEN clause, and each WHEN clause is given a full boolean expression to evaluate, e.g.:

CASE WHEN pct >= 20 THEN ...

In the other syntax, the CASE keyword is immediately followed by a scalar expression that is evaluated; each WHEN clause is given a scalar value to be tested for equality against the result of the first expression, e.g.:

CASE pct WHEN 20 THEN ...

You are trying to use the second syntax but give the WHEN clause a (partial) boolean expression. You simply can't do that.

I think the best solution is to simply use the first syntax. I.e. where you are writing CASE pct WHEN >=20 THEN ..., instead write CASE WHEN pct>= 20 THEN .... This is clear and allows you to make arbitrarily complex CASE statements.

If you are really attached to using the second, switch-like syntax, in some cases you can come up with a way of converting the test you want into an equality test. For your example, you could write:

CASE SIGN(pct-0.20) WHEN -1 THEN NULL ELSE 1 END

But this seems less clear to me than writing it the other way.