Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: Using Sum() in Having Clause

    Hi everyone,

    I have yet another question:

    I have this cases table that looks like:

    serial qty
    53565 1
    53566 2
    53567 3

    I am trying to select the rows where the sum of their quantity(qty) + 1 = 4.

    This is the syntax I am trying to use (which does not return any rows)


    select serial
    from cases
    group by serial
    having ((sum(qty) + 1) = 4)

    Desired results:

    serial
    53565
    53566

    I'm probably doing something real stupid up there...so any I'd appreciate any help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't test this right now, but I think you could use:
    Code:
    SELECT a.serial, b.serial
       FROM cases AS a
       JOIN cases AS b
          ON (b.serial <> a.serial)
       WHERE  3 = a.qty + b.qty
    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    Sorry should have specified this before, but the values "1" and "4" are variables that I continue to pass in so they have a potential to change:


    select serial
    from cases
    group by serial
    having ((sum(qty) + 1) = 4)

    I was hoping that the select statement would be flexible enough to accept the change...I'm not sure but aren't you supposed to be able to conduct arithmatic functions in the having clause?

    Thanks for the help!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, then:
    Code:
    SELECT a.serial, b.serial
       FROM cases AS a
       JOIN cases AS b
          ON (b.serial <> a.serial)
       WHERE  4 = 1 + a.qty + b.qty
    -PatP

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    I guess the example-data you gave in the original post is what is throwing me: you give only one row for each number. Nothing is being "sum()"ed, and I don't see how the answer you give as correct was determined.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's true, 53565 and 53566 with quantity of 1 and 2 respectively will not yield 4 when you add 1 for either of them...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you add one to the sum of the qty for both of the numbers, then you are Ok. 1 (the constant) plus 1 (the qty for 53565) plus 2 (the qty for 53566) gives 4 (the other constant). Kind of kinky logic, but it satisfies both the sample data and the spirit of the non-functioning code provided.

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    But GROUP BY will NOT add the quantity for both serial number, just for one, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Agreed, but the poster even said that the code they posted wasn't doing what they wanted. I tried to re-interpret what they meant into code that satisfied both the text description and the example they provided.

    Unless they respond with either yea or nay, we'll never know if I guessed right. Heck, I couldn't even test the code that I posted, but I think it should work.

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and with that in mind, - see you tomorrow
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jul 2004
    Posts
    53
    hey thanks for the feedback....Pat my fault for not specifying the changing variable in the first post...and also that the cases table is potentially going to have new values inserted into it...that was just the way my current table looked:

    I found something that would work in my scenario...feedback criticism more than welcome

    select serial
    from cases where
    ((select sum(qty) from cases + @requested) = @exists)

    I was trying to avoid having to do a 'select sum(qty)' but it seems to work with the different scenarios I have...

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow. I wouldn't have thought that was syntactically correct, much less that it would produce the results that you want. I obviously misunderstood the question by quite a bit.

    -PatP

Posting Permissions

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