Percentage of total in SQL

Sample data, 1 row per product per city:

city       product  revenue
Hyderabad  Laptop   120000
Hyderabad  Phone     60000
Hyderabad  Tablet    20000
Pune       Laptop    90000
Pune       Phone     10000

SELECT city, product, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY city), 1) AS pct_of_city,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1)                  AS pct_of_all
FROM sales
ORDER BY city, revenue DESC;

city       product  revenue  pct_of_city  pct_of_all
Hyderabad  Laptop   120000   60.0         40.0
Hyderabad  Phone     60000   30.0         20.0
Hyderabad  Tablet    20000   10.0          6.7
Pune       Laptop    90000   90.0         30.0
Pune       Phone     10000   10.0          3.3

SUM(revenue) OVER (PARTITION BY city) repeats the city total on every row of that city. SUM(revenue) OVER () has no partition, so it is the grand total. Dividing by them gives the share inside the city and the share overall, and all 5 rows stay in the result.

Alternative: compute the totals in a subquery or CTE and join back. It gives the same numbers, and it reads clearly when the total needs extra filters. The window version is shorter and avoids a second scan.

Three traps to mention

• Integer division. In engines such as PostgreSQL and SQL Server, 60000 / 300000 between two integers returns 0. Writing 100.0 * first forces decimal arithmetic. DuckDB and Spark SQL return a decimal for /, but the 100.0 habit keeps the query portable.
• Zero totals. If a total can be 0, wrap it in NULLIF(total, 0) so the query returns NULL instead of a divide-by-zero error.
• Filters change the total. A WHERE clause runs before the window function, so filtering to one product makes it 100% of the remaining rows. If you want shares of the full total, compute the percentage first and filter in an outer query.