# Thread: Using Sum() in Having Clause

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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.

6. Registered User
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...

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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?

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
...and with that in mind, - see you tomorrow

11. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579