SQL sum across rows

Question

I'm trying to make a query to obtain this result (Presto sql in case it's relevant)

| State  | Region   | City     | Sales-City | Sales-Region | Sales-State |
|--------|----------|----------|------------|--------------|-------------|
| Italy  | Lombardy | Milan    | 100        | 150          | 250         |
| Italy  | Lombardy | Como     | 50         | 150          | 250         |
| Italy  | Tuscany  | Florence | 75         | 100          | 250         |
| Italy  | Tuscany  | Prato    | 25         | 100          | 250         |
| France | Ile de F.| Paris    | 5          | 10           | 20          |
| ..     | ..       | ..       | ..         | ..           | ..          |

After writing the basics

SELECT State, Region, City,
       SUM(Sales) as "Sales-City"
FROM db
GROUP BY State, Region, City

But of course I'm stuck on the Sales-Region / Sales-State parts. What's the way to solve it in terms of performance? It seems ridiculous to have to write two (potentially more) separate WITH statements.

Answer 1

Use window functions!

select state, region, city,
       sum(sales) as sales_city,
       sum(sum(sales)) over(partiton by state, region) sales_region,
       sum(sum(sales)) over(partiton by state) sales_state
from db
group by state, region, city

Answer 2

Another way to solve this kind of problem would be to use grouping sets or rollup with the relevant combinations for aggregation -

SELECT State, Region, City,
       SUM(Sales)
FROM db
GROUP BY GROUPING SETS (
(State),
(State,Region),
(State,Region,City));

Or

SELECT State, Region, City,
       SUM(Sales)
FROM db
GROUP BY ROLLUP (State, Region, City);