Percentage of Total
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 24 of 27.
Each row's share of the whole is a window-function classic: divide the row's value by the SUM of the same column over the whole window.
SELECT product, revenue,
revenue * 100.0 / SUM(revenue) OVER () AS pct
FROM salesAn empty OVER () means "the whole result set". Add a PARTITION BY to compute the share within a group instead (e.g. OVER (PARTITION BY category) for share within each category).
Multiply by 100.0 (not 100) so the division stays a float. Otherwise SQLite's integer division would round small shares to zero.
Challenge
EasyAvailable tables and columns:
<strong>sales</strong>:<strong>category</strong>,<strong>product</strong>,<strong>revenue</strong>
For each row return:
categoryproductrevenuepct_of_category: the product's share of revenue within its category, rounded to 1 decimal place
Use a PARTITION BY on the window. Order by category, then pct_of_category descending.
Cheat sheet
Calculate each row's percentage share of a window using SUM() OVER ():
SELECT product, revenue,
revenue * 100.0 / SUM(revenue) OVER () AS pct
FROM sales- Empty
OVER ()= whole result set OVER (PARTITION BY category)= share within each group- Use
100.0(not100) to avoid integer division rounding to zero
Try it yourself
SELECT category, product, revenue,
-- pct as a share within the category, use PARTITION BY
FROM sales
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Beyond the Basics
6Analytics Recipes
Top-N per GroupPercentage of TotalRunning TotalKeeping One Row per KeyRecap - Top Customers