Programming
sql sqlite average calculated-columns
Updated Sun, 14 Aug 2022 17:01:13 GMT

How to obtain ratio of column X over column Y in SQLlite?


I am supposed to obtain the following result: Demographics: Ratio of Men and Women Write a query that shows the ratio of men and women (pop_male divided by pop_female) over the age of 18 by county in 2018. Order the results from the most female to the most male counties.

county  ratio
0   MADERA  0.909718
1   YOLO    0.932634
2   CONTRA COSTA    0.936229
3   SACRAMENTO  0.943243
4   SHASTA  0.944123

This preview is limited to five rows.

A select * from population shows base data in this format:

    fips    county  year    age pop_female  pop_male    pop_total
0   6001    ALAMEDA 1970    0   8533    8671    17204
1   6001    ALAMEDA 1970    1   8151    8252    16403
2   6001    ALAMEDA 1970    2   7753    8015    15768
3   6001    ALAMEDA 1970    3   8018    8412    16430
4   6001    ALAMEDA 1970    4   8551    8648    17199

.....and so on from ages 0-100 for all years 1970- 2018. State is CA

I tried using:

select county, (sum(pop_male) / sum(pop_female)) as ratio
from population group by county, year having age > 18 and year = 2018;

output was instead:

 county ratio
ALAMEDA 0
ALPINE  1
AMADOR  1
BUTTE   0
CALAVERAS  0
COLUSA  1
CONTRA COSTA   0

Note: I am aware I haven't done any order by yet as I am not even outputting correct data.

SQLRaptor gave me a suggestion and I tried:

select county, (CAST(sum(pop_male) AS DECIMAL(1,6)) / (CAST(sum(pop_female) AS DECIMAL(1,6)) as ratio
from population group by county, year having age > 18 and year = 2018 

this gave me the response:

sqlite:///../Databases/population.sqlite3 (sqlite3.OperationalError) near "as": syntax error [SQL: select county, (CAST(sum(pop_male) AS DECIMAL(1,6)) / (CAST(sum(pop_female) AS DECIMAL(1,6)) as ratio from population group by county, year having age > 18 and year = 2018]

I took Esteban P's suggestion and used:

select county, (SUM(CAST(pop_male AS float)) / SUM(CAST(pop_female AS float))) as ratio from population group by county, year having age > 18 and year = 2018 order by ratio

This worked.




Solution

Don't want to override the SQLRaptor's helpful response in comments, but for the sake of completeness :)

SQL treats integer division as integer, therefore truncating it. To avoid that -- cast at least one of the values to a floating point data type (e.g. REAL or FLOAT for SQLite -- check the manual on data types here: https://www.sqlite.org/datatype3.html)







External Links

External links referenced by this document: