Calculate row value as a proportion of the sum of all rows using In-DB

[akasubi]

How can I calculate the value of one row of data as a proportion of the sum of all rows in the data, using In-DB?

E.g. 

ID	Value	Proportion
A	10	10%
B	30	30%
C	20	20%
D	40	40%

The total sum of all rows is 100.

Thanks

afk

[Thableaus · Accepted solution]

Hi @akasubi 

Which DB are you using?

This will definitely involve a Summarize Tool, to calculate the total column.

Then you might need to append this total to all of your rows, and do something like (Value/Total) to calculate the proportion.

You could give us a better picture of your data and specify which database you're working with.

Cheers,

[akasubi]

Hi @Thableaus 

I'm connected to SQL server. I did think to do as you suggested but wasn't sure if this was the correct approach.

From another question I posted, I will need to create dummy fields to append the total sum to all my rows?

[Thableaus]

@akasubi 

Either it's that or creating a SQL query to do this calc for you (from what I know)

Using Alteryx In-DB tools, I think this is the best way to go (the most intuitive one).

Creating the Total Column should not be a problem. You can drop it after calculating the Proportion column.

Cheers,

[geraldo]

Hi, The query that I suggest looks like this: select ID as ID, count(*) as Value , count(*) / max( (select sum(Value) from ( select count(*) as Value , ID from TableName group by ID) )) * 100 as Proportion from TableName group by ID;

[geraldo]

Hi,

The query that I suggest looks like this:

select ID as ID,
count(*) as Value ,
count(*) / max( (select sum(Value)
from ( select count(*) as Value ,
ID
from TableName group by ID) )) * 100 as Proportion
from TableName group by ID;