Calculate share based on other column values

user_12625679 0
Viewed 32

I would like to calculate the share of total orders for a given country while also showing the country column.

Sample output:

customer_id country count_orders
20323 GB 43
20323 US 94

Expected output:

customer_id country count_orders share_total_orders
20323 GB 43 0.313
20323 US 94 0.686
presto sql
2 Answers

Yet another option is using the SUM window function to catch your total orders:

SELECT customer_id, 
       country, 
       count_orders, 
       count_orders / SUM(count_orders) OVER() AS share_total_orders
FROM orders
user_124928900

You could cross join on the sum of all the orders and divide by it:

SELECT     customer_id, country, count_orders, 
           count_orders / total_orders AS share_total_orders
FROM       orders
CROSS JOIN (SELECT SUM(count_orders) AS total_orders
            FROM   orders) s
user_24227760
Related