CODEWITHSUNDEEP
sqlsqlite
Beans On Toast
Beans On Toast

Reputation: 1081

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?

Upvotes: 0

Views: 112

Answers (4)

Rajat
Rajat

Reputation: 5803

I think you are looking for a difference between percentage of (4,5) and (1,2) for each group. Since null values are ignored in aggregation, you could simply do

(avg(score in (4,5)) - avg(score in (1,2)))*100

Upvotes: 1

Nazim
Nazim

Reputation: 639

I advise multiplying by 1.0 each sum so that it doesn't round the sum to int value which would result in zero(0) for 'bbc' group.

Here is the updated version of your query(Check also SQL fiddle http://sqlfiddle.com/#!5/5fca1/8):

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

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

From your manual example, You may first sum all the not null values, and then use that value in your code -

WITH NN_SUM AS (SELECT SUM(score is not null) NOT_NULL_SUM
                FROM my_table)
SELECT website,
       (((sum(score IN (4,5)) - sum(score IN (1,2))) / NOT_NULL_SUM) * 100 as new_column
FROM my_table
CROSS JOIN NN_SUM
GROUP BY website;

Upvotes: -1

Peter Dongan
Peter Dongan

Reputation: 2306

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?

Upvotes: 2

Related Questions