Post reply

Divide by subsets of same table

  • September 14, 2009 at 1:52 pm

    #156215

    This doesn't seem like it should be this hard... maybe it's just a Monday thing.

    I've got some data in a table and I want to divide a subset of it by another. Specifically when I'm looking at a specific status and then the number of records with that status over 90 days old. I've written the query below but I get an incorrect syntax error at the , between the two selects. Now I'm stumped.

    select x / y

    from

    (select count(*) x from My_Table where Status = 'O' and Days_Old < '90'),
    (select count(*) y from My_Table where Status = 'O')

  • September 14, 2009 at 2:05 pm

    #1053436

    You can put one of the selects into the original and just make it a subselect with an alias of y. Does this work?

    select x / y

    from

    (select count(*) x,

    (select count(*) from My_Table where Status = 'O') y

    from My_Table

    where Status = 'O'

    and Days_Old < '90') test

  • September 14, 2009 at 2:14 pm

    #1053440

    You just missed assigning an alias name.

    Try this:

    select x / y

    from

    (select count(*) x from My_Table where Status = 'O' and Days_Old < '90') as A,

    (select count(*) y from My_Table where Status = 'O') as B

  • September 14, 2009 at 2:29 pm

    #1053447

    Matt, s

    Both examples "work"!! Thanks!

    However, the reason work is in quotes is that my result comes back as 0 in both cases. However, if I just try to retrieve the values of x and y (i.e. x, y instead of x / y) those come back correctly. Weird.

  • September 14, 2009 at 2:38 pm

    #1053450

    its that integer division raising it's ugly head again;

    both counts are INT values, and SQL figures if you do any math(add,subtract, muultiply or divide) to two integers, the answer must be an int too!

    select x / y

    --should be select

    x /( y * 1.0)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 14, 2009 at 3:17 pm

    #1053468

    Thanks Lowell, that took care of the division result.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply