开发者

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,

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜