How do I calculate the % of a row value to the column total?
I have a dataset with three fields: date, customer, and clicks.
How do I create a new column that shows the % of each row to the total sum of the clicks column?
-
Official comment
In c9ql, you can try using the syntax below:
select *
append
select sum(clicks) as ttl_clicks;
select *, (clicks/ttl_clicks)*100 as pct_gmv
To break this down:
This part of the syntax uses the select * and append to string two statements together. This creates a new column "ttl_clicks", which is the sum of the clicks column.
select *
append
select sum(clicks) as ttl_clicks;
The following syntax takes the value of each row in the "clicks" column, and divides it by the "ttl_clicks" column to get the percentage. Multiplying by 100 makes it an actual percentage (i.e. 96.1) instead of a decimal (i.e. 0.961).
select *, (clicks/ttl_clicks)*100 as pct_gmv
You can apply the round() function to this as well:
select *, round((clicks/ttl_clicks)*100,2) as pct_gmv
If you want to do this for multiple columns, i.e. "clicks" and "delivered", you can use the syntax below. The key is to make sure that for each new column you are taking the sum() of, that you use append each time.
select *
append
select sum(clicks) as ttl_clicks
append
select sum(delivered) as ttl_delivered;
select *,
(clicks/ttl_clicks)*100 as pct_gmv
(clicks/ttl_delivered)*100 as pct_delivered
Comment actions -
is there a way to this also per drag and drop?
Please sign in to leave a comment.
2 comments