我应该得到以下结果:人口统计学:男女比例写一个查询,显示2018年按县划分的18岁以上的男女比例(pop_male除以pop_female)。将结果从女性最多的县排序到男性最多的县。
county ratio
0 MADERA 0.909718
1 YOLO 0.932634
2 CONTRA COSTA 0.936229
3 SACRAMENTO 0.943243
4 SHASTA 0.944123
此预览限制为五行。
select * from population以以下格式显示基本数据:
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等,从0到100岁,所有年份1970- 2018年。状态为CA
我尝试使用:
select county, (sum(pop_male) / sum(pop_female)) as ratio
from population group by county, year having age > 18 and year = 2018;
相反,输出结果是:
county ratio
ALAMEDA 0
ALPINE 1
AMADOR 1
BUTTE 0
CALAVERAS 0
COLUSA 1
CONTRA COSTA 0
注意:我知道到目前为止我还没有做任何订单,因为我甚至没有输出正确的数据。
SQLRaptor给了我一个建议,我试了一下:
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
这给了我这样的回答:
sqlite sqlite:/../Databases/Popation.sqlite3 (sqlite3.OperationalError)在“AS”附近:语法错误
:select county,(CAST(sum(pop_male) as DECIMAL(1,6)) / (CAST(sum(pop_female) AS DECIMAL(1,6)) as比率来自人口分组,年龄> 18岁且年份= 2018
我采纳了Esteban P的建议,并使用:
从人口分组中选择县(SUM(CAST(pop_male AS float)) / SUM(CAST(pop_female AS Float)作为比率,年龄> 18岁的年份和年份= 2018按比率排序
这起作用了。
发布于 2019-03-27 07:35:43
我不想在注释中覆盖SQLRaptor的有用响应,但为了完整性:)
SQL将整数除法视为整数,因此将其截断。要避免这种情况--将至少一个值转换为浮点数据类型(例如,对于SQLite,使用REAL或FLOAT --请查看数据类型手册:https://www.sqlite.org/datatype3.html)
https://stackoverflow.com/questions/55367362
复制相似问题