Addition in SQLite WHERE

I want to do a query where I check the sum of two columns in my SQLite database is it possible?

I ha开发者_高级运维ve tried with this and it executes alright but doesn't find anything:

SELECT * FROM my_table WHERE (col1 + col2) > my_value.

(I'm using the SQLite3 db in Android)

That's perfectly valid syntax (though I'd personally leave off the parentheses).

You should check that you actually have rows that meet this condition.

This can be done with a query like:

select col1 + col2 as xx from my_table order by xx asc

and examining the last row.

I'm on SQLite version 3.7.7.1, and it seems to work fine for me.

sqlite> create table blah (x int, y int)
   ...> ;
sqlite> insert into blah values (1,1);
sqlite> insert into blah values (5,8);
sqlite> insert into blah values (0,-1);
sqlite> select * from blah where x+y>1;
1|1
5|8
sqlite> select * from blah where x+y>2;
5|8
sqlite>

Can you give me more info about the data you're working with?

ETA: It also appears to work when comparing the sum of two columns to another column:

sqlite> create table blurg (x int, y int, z int);
sqlite> insert into blurg values (2,3,1);
sqlite> insert into blurg values (1,1,3);
sqlite> select * from blurg where x+y>z;
2|3|1

Your syntax is correct, just make sure there is something in there. For diagnostics, start with

SELECT col1, col2, (col1+col2) AS thesum, my_value FROM my_table

And do the comparison yourself. Usually you'll find out that either col1 or col2 is NULL and it does not work because of that.

It may be that your data is in the wrong format as SQLITE is very (perhaps too) forgiving of type errors.

What do you see when you run:

SELECT col1, col2 , col1 + col2 FROM my_table;