Why is this query giving results below 100
(1) By Carrington (BlakeCAtlanta) on 2020-06-02 12:01:58 [link] [source]
I've got a really basic query that is supposed to find the DateOfReport and Death Count from each group (Country) when the count is 100 or higher.
SELECT MIN(NumDeaths), DateOfReport
WHERE NumDeaths >= 100
GROUP BY NameOfCountry
So why is it giving me results like "11" and "100418", when most of the Countries have a record that states 100 deaths?
(2) By Stephan Beal (stephan) on 2020-06-02 12:05:46 in reply to 1 [source]
The problem is almost certainly that your numbers are being stored as text instead of numbers. Remember that the data types declared in the schema colhmns are meaningless - how you actually store them is the important thing. (This is a common mistake which many, if not most, of us have made.)
(3) By Richard Hipp (drh) on 2020-06-02 12:22:46 in reply to 1 [link] [source]
What does the following query how:
SELECT min(NumDeaths+0), DateOfReport FROM DailyReports WHERE NumDeaths >= 100 GROUP BY NameOfCountry;
+0 on the end of
NumDeaths ensures that you are dealing with
numbers instead of strings. You might also phrase it as:
.... min(CAST(NumDeaths AS Integer)) ....
(4) By Simon Slavin (slavin) on 2020-06-03 00:43:19 in reply to 1 [link] [source]
CREATE statement for that table ? Does it say that those columns are meant to be numbers ? If not, they're probably being stored as strings.