lundi 11 janvier 2016

SQLite subquery math returns wrong values

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