...

View Full Version : SQL subtract



micci73
09-18-2006, 05:43 PM
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

nikkiH
09-18-2006, 06:15 PM
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

micci73
09-18-2006, 06:21 PM
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

Taylor_1978
09-18-2006, 09:36 PM
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...

nikkiH
09-18-2006, 10:13 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum