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
Solved! Go to 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,
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?
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,
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;