How to calculate sum of group in SQL?

I’m trying to find how to calculate a new SQL column that works on a formula involving ‘NA’ values. I think that I am not getting my desired result because of either the ‘NA’ values or because of my grouping. Please see the table and formula below:

My table is the following:

website session score
 google    1       1
 google    2       NA
 bbc       3       2
 bbc       4       4
 bbc       5       5

The formula i am using to created a new calculated field is the following:

select
    website,
    (sum(score IN (4,5))/sum((score is not null)) - sum(score IN (1,2))/sum(score is not null))*100 as new_column
from 
    my_table
group by 
    website

Essentially the formula is trying to do the following:

Sum up those values in the score column that are either 4 or 5, then divide them by the sum of the numbers in the score column that do exist.

Then deduct the following:

Sum up those values in the score column that are either 1 or 2, then divide them by the sum of the numbers in the score column that do exist.

Finally:

Multiply the entire output by 100

The formula has to be applied to each group.

So you should have the following desired result:

Taking only the first group of “google”

You have 4 not null values. you have 2 values whereby the value is IN (1,2) and 0 values in (4,5).

so you would have the following formula:

((0/4)-(2/4))*100

-2/4 * 100

= -50

however, when calculating this in SQL i recieve the value -100. which does not make sense to me. can anyone help?

Answer

You have one not null values for Google. The sum of it is one. So your formula is resulting in (0-1/1)*100 which is -100, which is what it is giving.

What I think you want is the count of all Google records divided by the count of all non-null records?

Advertisement

Answer