We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL grouping question

jstorm13
jstorm13 asked
on
Medium Priority
251 Views
Last Modified: 2012-05-07
I have a table with these fields.  (item_num, location, and value)  

Each item_num might have many entries, each with a different location for the item.

Only one of the item locations should have a value of true (1).  All others should have
a value of false (0).  This lets me know the primary location for the item.  All the other
locations for that item will have a 0 and be secondary locations.

What I want to know is which item_num (item numbers) have all location values of 0 and none with 1.

So:

Show me all item_num where there is no 1 value set for any of the locations.

Can someone tell me the SQL statement for this please?  
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Assuming that [value] is a bit data type...

SELECT item_num
FROM SomeTable
GROUP BY item_num
WHERE MAX(CAST([value] AS int)) = 0
CERTIFIED EXPERT
Top Expert 2010

Commented:
If [value] is already [tiny][small]int, then this simpler construction should work:

SELECT item_num
FROM SomeTable
GROUP BY item_num
WHERE MAX([value]) = 0

Author

Commented:
It complains about putting a Where after a group by.

If I revers the group by and where then it is in proper order however i get the following error.

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That worked ralmada Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.