Global finance teams need revenue summaries at multiple geographic levels simultaneously: individual countries, regional subtotals, and a worldwide grand total — all in a single query. This kind of hierarchical rollup lets executives drill from the global number down to individual markets without running separate queries. Using the orders table (which includes region and country columns), return region, country, and revenue at three levels: per-country detail, per-region subtotal, and a grand total. Label subtotal/total rows with 'All Regions' / 'All Countries'. Order detail before subtotal, subtotal before grand total.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| region | TEXT |
| country | TEXT |
| created_at | DATE |
orders
| id | customer_id | amount | region | country | created_at |
|---|---|---|---|---|---|
| 1 | 1 | 500.00 | AMER | US | 2024-01-01 |
| 2 | 2 | 200.00 | EMEA | DE | 2024-01-02 |
| 3 | 3 | 300.00 | EMEA | UK | 2024-01-03 |
| region | country | revenue |
|---|---|---|
| AMER | US | 500.00 |
| AMER | All Countries | 500.00 |
| EMEA | DE | 200.00 |
| EMEA | UK | 300.00 |
| EMEA | All Countries | 500.00 |
| All Regions | All Countries | 1000.00 |
AMER has one country (US, 500). EMEA has two countries (DE 200, UK 300) summing to 500. The grand total across both regions is 1000.