Why is this query giving results below 100

(1) By Carrington (BlakeCAtlanta) on 2020-06-02 12:01:58 [link]

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

FROM DailyReports 

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

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]

What does the following query how:

~~~~~
   SELECT min(NumDeaths+0), DateOfReport
     FROM DailyReports
    WHERE NumDeaths >= 100
    GROUP BY NameOfCountry;
~~~~~

The `+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]

What's the <code>CREATE</code> statement for that table ?  Does it say that those columns are meant to be numbers ?  If not, they're probably being stored as strings.