Schema and sample data
| product_id | name | category | sales |
|---|---|---|---|
| 1 | Laptop | Electronics | 600 |
| 2 | Phone | Electronics | 300 |
| 3 | Cable | Electronics | 100 |
| 4 | Blocks | Toys | 250 |
| 5 | Puzzle | Toys | 100 |
| 6 | Kite | Toys | 50 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
sales INT NOT NULL
);
INSERT INTO Products (product_id, name, category, sales) VALUES
(1, 'Laptop', 'Electronics', 600),
(2, 'Phone', 'Electronics', 300),
(3, 'Cable', 'Electronics', 100),
(4, 'Blocks', 'Toys', 250),
(5, 'Puzzle', 'Toys', 100),
(6, 'Kite', 'Toys', 50);From the Products table, return each product's name, category and sales, plus a fourth column pct_of_category that shows what percent of its category's total sales that product represents. Compute the percent as sales * 100.0 / (total sales for that category) and round it to two decimals. Sort by category ascending, then by sales descending.
Expected result
| name | category | sales | pct_of_category |
|---|---|---|---|
| Laptop | Electronics | 600 | 60.00 |
| Phone | Electronics | 300 | 30.00 |
| Cable | Electronics | 100 | 10.00 |
| Blocks | Toys | 250 | 62.50 |
| Puzzle | Toys | 100 | 25.00 |
| Kite | Toys | 50 | 12.50 |
Show hint
You need the category total on every row without collapsing the rows, so reach for a window function: SUM(sales) OVER (PARTITION BY category). Divide the row's sales by that total. Multiply by 100.0 (not 100) so the division happens in decimal rather than integer arithmetic.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT
name,
category,
sales,
ROUND(sales * 100.0 / SUM(sales) OVER (PARTITION BY category), 2) AS pct_of_category
FROM Products
ORDER BY category, sales DESC;The key is SUM(sales) OVER (PARTITION BY category). Unlike a GROUP BY aggregate, a windowed SUM does not collapse the rows: it computes the total for each category and attaches that total to every row in the category, so you keep one output row per product. Electronics sums to 1000 and Toys sums to 400, and each product's sales is divided against its own category total.
The 100.0 literal matters. If you wrote sales * 100 / SUM(...) with integer operands, SQL Server would perform integer division and truncate the fractional part, so 600 * 100 / 1000 would still be fine but 250 * 100 / 400 would give 62 instead of 62.5. Writing 100.0 makes the expression decimal, so the fraction is preserved before ROUND(..., 2) trims it to two places. This is the same integer division trap that surprises people writing plain averages.
A common alternative is a correlated subquery or a self join to fetch the category total, but the window function is shorter, reads in one pass, and avoids repeating the table. The ORDER BY category, sales DESC at the end sorts categories alphabetically and, within each, lists the biggest seller first. For the general pattern see percent of total.