SQL for columns A, B, C: group by A, compute share of B equals C for each A

Database is Oracle. I have a single table with three columns, A, B and C.

• A is a string column
• B is an int column
• C is an int column

I need an SQL select that groups on A and returns the share (in percent) where B = C = [particular int value] for each value of A.

Something like

SELECT A, percentBEqC
..开发者_开发问答.
GROUP ON A

---

SELECT A
     , COUNT( CASE WHEN B = C AND C = 256
                   THEN 1
              END 
            )
       / COUNT(*) AS percentBEqC
FROM YourTable
GROUP BY A

You may want to check if B = 256 AND C = 256 has better performance.

---

Another way for your problem would be:

SELECT ta.A
     , COALESCE(cnt,0) / cntAll AS percentBEqC
FROM
    ( SELECT A
           , COUNT(*) AS cntAll
      FROM YourTable
      GROUP BY A
    ) ta
  LEFT JOIN
    ( SELECT A
           , COUNT(*) AS cnt
      FROM YourTable
      WHERE B = 256 AND C = 256
      GROUP BY A
    ) ts
    ON ts.A = ta.A

---

Another way:
_{..once the syntax is correct ;/}

SELECT A, (COUNT(*) / (SELECT COUNT(*) FROM mytbl b WHERE b.A = a.A)) AS percentBEqC
  FROM mytbl a
 WHERE B = particular_value
   AND C = particular_value
 GROUP BY A;

Returns only rows where percentBEqC > 0,