0

Lets say we have 3 columns, all integers, the first the id, the second X and the third Y, where X is 1 when an instance occurred, 0 when not, and Y is 1 when an opportunity for that instance occurred and 0 when not.

=================

|  id  | X  | Y | 

=================

| 1    | 1  | 1 | 

| 1    | 0  | 1 | 

| 1    | 0  | 0 | 

| 1    | 1  | 1 | 

| 2    | 0  | 0 | 

| 2    | 0  | 1 |

| 2    | 1  | 1 |  

| .... |    |   | 

=================

What I want to do is go through and find basically X / Y as a percentage
i.e:

(total number of times an instance occurred / total number of opportunities for that to occur, and this all to be grouped by id) * 100

The query I have which works (but I wonder if there is a more efficient method) is

select id, (CASE WHEN ( sum(Y)) <> 0 THEN ((sum(X))/( sum(Y))) ELSE 0 END) ) * 100) from table GROUP BY id

I wonder if it is more efficient to some how do

sum(cast(case where Y <> 0 THEN (X/Y) ELSE 0 END 0...
Rafael
  • 7,002
  • 5
  • 43
  • 52
oracle3001
  • 1,090
  • 19
  • 31
  • Not quite a duplicate of this issue, but the answers there outline most of the possible approaches: http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql – mr.Reband May 17 '13 at 13:44
  • I wonder if it is more efficient to some how do sum(cast(case where Y <> 0 THEN (X/Y) ELSE 0 END 0... – oracle3001 May 17 '13 at 13:48
  • If you deal with `integers` only then you may want to `CAST` either of the sums to force decimal division. I mean it's too late to `CAST` the result. – PM 77-1 May 17 '13 at 14:34
  • Thanks everybody...have solved this now using COALESCE and nullif – oracle3001 May 17 '13 at 14:36

0 Answers0