[SELECT] How to include percentage?
(1) By Gilles on 2020-11-29 12:22:14 [link] [source]
Hello, This is a newbie question. Based on the census, in a SELECT used to see where people drive to work, I'd like to include the percentage next to the absolute numbers so that people don't have to add a formula in their favorite spreadsheet. In pseudo-SQL : #For my hometown, show me how many people drive to work, and where SELECT COUNT(*),(COUNT(*)/TOTAL(census))*100,zip.name FROM census,zip WHERE census.residence_zip="12345" AND census.mode="3" AND census.work_zip=zip.zip GROUP BY census.work_zip; What would be the right way to handle this in SQLite? As a subquery to create a variable that can then be referenced at the beginning of the query? Thank you.
(2) By Clemens Ladisch (cladisch) on 2020-11-29 20:13:05 in reply to 1 [link] [source]
Percentage of what?
TOTAL(census) means the number of all rows in the
census table, then you can write:
SELECT COUNT(*), (COUNT(*) / (SELECT COUNT(*) FROM census)) * 100, ...
(3) By anonymous on 2020-11-29 20:25:42 in reply to 2 [link] [source]
Won't this 1. hit division by zero 2. truncate to integer percentage?
(4) By Clemens Ladisch (cladisch) on 2020-11-29 20:50:46 in reply to 3 [link] [source]
Division by zero is not possible because the GROUP BY would not generate any groups in that case.
To avoid integer trunaction, use:
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM census)
(5) By Gilles on 2020-11-30 20:35:36 in reply to 2 [source]