CODEWITHSUNDEEP
sqlsqlite
George Strawbridge
George Strawbridge

Reputation: 21

SQL greater than query is returning incorrect results

I'm new to SQLite and looking to expand my skill set considerably. I have created a simple database with two tables,'products' and 'shopping'.

I used the following query to create my first table and values

sqlite> CREATE TABLE shopping(Product TEXT PRIMARY KEY, Quantity       NOT NULL);
sqlite> INSERT INTO shopping(Product, Quantity) VALUES ('Jam', 1);
sqlite> INSERT INTO shopping(Product, Quantity) VALUES ('Bread', 2);
sqlite> INSERT INTO shopping(Product, Quantity) VALUES ('Tea', 5); 
sqlite> INSERT INTO shopping(Product, Quantity) VALUES ('Cereal', 1);

and the following query to create my second table

sqlite> CREATE TABLE ProductData(Product TEXT PRIMARY KEY, Price NOT NULL);
sqlite> .import /home/solidsnake/Documents/ProductData.csv ProductData

values for this table where imported via a .csv file with

sqlite> SELECT Product, Price
...> FROM products;

giving the output:

Jam,250
Tea,150
Cereal,120
Eggs,170
Cheese,320
Potatoes,80
Treacle,80
Bananas,100
Bread,230
Caviar,1000

The main problem I'm now running into is I need to use an SQL query to find out what products in the shop have a cost higher than 120.

After having a thorough search on the internet I found that the correct query to provide me with the correct input is

SELECT * FROM "products" WHERE Price > '120';

However, this gives me the output:

Jam,250
Tea,150
Eggs,170
Cheese,320
Potatoes,80
Treacle,80
Bread,230

As you can see, both potatoes and treacle have a price of 80 which is less than 120. Also caviar is not provided in the output which seems strange since it has a price of 1000.

Would anybody be able to point me in the right direction as to what the problem is with my syntax and why it's producing an incorrect output.

PS: Sorry for the long winded post, but I wanted to provide as much information as possible. I'm new to stackoverflow and have tried to follow the 'How to ask a question' post as closely as possible.

Thanks.

edit: SOLVED

Thanks lad2025.I ended up removing the shopping table and recreating it, incorporating your suggestion. The query now works perfectly.

Upvotes: 1

Views: 775

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

Please do not compare with string value(use integer):

SELECT * FROM "products" WHERE Price > 120; -- instead of '120'

String sorting is different than integer, so '80' > '120' is correct and '1000' < '120' is also correct.

SELECT '80' > '120', 80 > 120
-- 1 0

SELECT '1000' > '120', 1000 > 120
-- 0 1

DBFiddle Demo

Plus define Quantity datatype:

CREATE TABLE shopping(Product TEXT PRIMARY KEY, Quantity   INT    NOT NULL);

Upvotes: 1

Related Questions