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;