Calculate share based on other column values-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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