How to get overall summary of grouped columns in SQL in a separate column?

I have a grouped query that looks like this that I got from the following query:

SELECT 
name, 
warehouse, 
COUNT(distinct orders) total_orders,
AVG(late_ship_flag) pct_late_ship,
SUM(units) sum_units,
FROM table1
GROUP BY 
name, 
warehouse

The Result is the following:

+----------+-----------+--------------+---------------+-----------+
| name     | warehouse | total_orders | pct_late_ship | sum_units |
+----------+-----------+--------------+---------------+-----------+
| company1 | 1         | 2            | 0             | 5244      |
+----------+-----------+--------------+---------------+-----------+
| company1 | 2         | 19           | 0.125         | 138264    |
+----------+-----------+--------------+---------------+-----------+
| company1 | 3         | 21           | 0.08952519    | 102681    |
+----------+-----------+--------------+---------------+-----------+
| company1 | 4         | 23           | 0.118733509   | 45456     |
+----------+-----------+--------------+---------------+-----------+
| company1 | 5         | 12           | 0.077922078   | 18396     |
+----------+-----------+--------------+---------------+-----------+

How I measure performance is the percentage of times there is a late shipment by a warehouse, so I would like to have a column that shows at the company level the average percentage across every warehouse. How can I get the following output in a single query? My only thought on how I would be able to do this would be to make temporary table with the company’s average and join that to the grouped output above.

ideal output:

+----------+-----------+--------------+---------------+-----------+------------------+
| name     | warehouse | total_orders | pct_late_ship | sum_units | average_pct_late |
+----------+-----------+--------------+---------------+-----------+------------------+
| company1 | 1         | 2            | 0             | 5244      | .10287           |
+----------+-----------+--------------+---------------+-----------+------------------+
| company1 | 2         | 19           | 0.125         | 138264    | .10287           |
+----------+-----------+--------------+---------------+-----------+------------------+
| company1 | 3         | 21           | 0.08952519    | 102681    | .10287           |
+----------+-----------+--------------+---------------+-----------+------------------+
| company1 | 4         | 23           | 0.118733509   | 45456     | .10287           |
+----------+-----------+--------------+---------------+-----------+------------------+
| company1 | 5         | 12           | 0.077922078   | 18396     | .10287           |
+----------+-----------+--------------+---------------+-----------+------------------+

Answer

If you want the average percentage across all the warehouses, then you can use:

AVG(AVG(late_ship_flag)) OVER (PARTITION BY name) as overall_pct_late_ship

Advertisement

Answer