Solved

SQL grouping question

Posted on 2009-07-01
5
198 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?  
0
Comment
Question by:jstorm13
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24755927
Assuming that [value] is a bit data type...

SELECT item_num
FROM SomeTable
GROUP BY item_num
WHERE MAX(CAST([value] AS int)) = 0
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24755933
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
0
 

Author Comment

by:jstorm13
ID: 24755975
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
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24755982
SELECT item_num
FROM SomeTable
GROUP BY item_num
having MAX([value]) = 0
0
 

Author Comment

by:jstorm13
ID: 24756010
That worked ralmada Thanks.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create a linked  server to connect ot an Access Database. 26 46
Need help with a query 3 46
Delete from table 6 46
Replace Dates in query 14 20
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now