SQLite Forum

Why is this query giving results below 100
Login

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

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 [link] [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;

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 [source]

What's the 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.