I'm querying a database to calculate percentages of calls by state. The first query pulls data correctly but when I try to do the needed math it doesn't return the correct values.
SELECT state, count(call) as Calls, (Select count(call) FROM test) as TotalCalls
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER state ASC;
State Calls TotalCalls
----- ----- ----------
Alabama 416 32149
Alaska 31 32149
Arizona 685 32149
Arkansas 216 32149
Now changing the query to do the math it returns 0. Answers for the first three should be 1.29, 0.09, 2.13
SELECT state, ROUND((count(call)/(Select count(call) FROM test) * 100.00), 2) AS StatePct
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER BY state ASC;
State StatePct
----- --------
Alabama 0.0
Alaska 0.0
Arizona 0.0
Arkansas 0.0
California 0.0
Colorado 0.0
Thinking that my SQL wasn't malformed, I reversed the calculations and it works!
SELECT state, ROUND(((Select count(call) FROM test)/count(call) * 100.00), 2) AS StatePct
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER BY state ASC;
State StatePct
----- --------
Alabama 7700.0
Alaska 103700.0
Arizona 4600.0
Arkansas 14800.0
California 700.0
Colorado 3700.0
Connecticut 5500.0
While I don't think this has a bearing it's SQLite3 on OSX.
Aucun commentaire:
Enregistrer un commentaire