SQLite Forum

[SELECT] How to include percentage?
Login

[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?

If 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 [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 [link] [source]

Thank you!