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?
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.)
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)) ....