Percent of total in SQL (and the integer division trap)

Why the usual answer is wrong

SELECT category,
  SUM(amount) * 100 / (SELECT SUM(amount) FROM sales) AS pct
FROM sales
GROUP BY category;

Every number here is a whole number, so the division is whole-number division: Books shows 26 instead of 26.7, and the shares don't add up to 100.

The answer

SELECT category,
  ROUND(100.0 * SUM(amount) / (SELECT SUM(amount) FROM sales), 1) AS pct
FROM sales
GROUP BY category;

Writing 100.0 makes the calculation decimal from the start. ROUND(…, 1) then keeps one decimal place.

SQLite, PostgreSQL and SQL Server all divide whole numbers this way; MySQL doesn't. In an interview, 100.0 is the safe habit.

SELECT category,
  ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM sales
GROUP BY category;