SQL subtract

hi all,

i did a search and didn't find what i was looking for, so don't think this is a duplicate post.

i want to subtract the counts of two statements. example:

statement 1:
select count (done) from mytable where user_id = 1 and done = 1

statement 2:
select count (maintained) from mytable where user_id = 1 and maintained = 1

so, what i really want as a result is statement 1 - statement 2, but i've tried several different ways and all if i don't get an error, then i get an answer of 0. any suggestions?

thanks,

micci

try this.

Tested successfully in SQL server, so here's to hoping it's the same for MySQL. :)

select
(select count (done) from mytable where user_id = 1 and done = 1) -
(select count (maintained) from mytable where user_id = 1 and maintained = 1)
as diff

Thanks. That worked. Although, I did change the first line to "Select distinct." Without distinct it returned the answer 940 times. Weird.

Thanks again,

micci

I haven't tested it with count but I have a similar query with sum, perhaps give this a try:

select count((done)-(maintained)) as diff...

Thanks. That worked. Although, I did change the first line to "Select distinct." Without distinct it returned the answer 940 times. Weird.
Thanks again,
micci

Welcome.
And not that odd, really, it depends on the DBMS which table it uses for the calculation, even if no rows are selected from a real table. My guess is that MySQL is using a temp table, or the table you were currently in when you exec'd the code.
In Oracle, you can select from "dual" to solve this. Not sure for MySQL.