I have a grouped query that looks like this that I got from the following query:
x
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 |
+----------+-----------+--------------+---------------+-----------+------------------+
Advertisement
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