Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: SQL subtract

  1. #1
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    try this.

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

    Code:
    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

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Jun 2003
    Location
    Australia
    Posts
    528
    Thanks
    8
    Thanked 8 Times in 8 Posts
    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...

  • #5
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Quote Originally Posted by micci73 View Post
    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.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •