Query help

[Cloonalt]

I have a table with multiple instances of Field 1. Field 2 may be 0 or 1, for instance:

Field 1 Field2
ABC Company 1
ABC Company 1
ABC Company 0
DEF Company 0
DEF Company 0
DEF Company 0

Can I write a query to get only Companies where there are all 0s?

Any help appreciated.

Thanks.

 

[SQLDenis]

select * from table where ColumnNotField2 =0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Ok I will not confuse you

select * from table where Field2 =0

but fields are columns in SQL server

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Cloonalt]

Thanks.

But in your query, I'll get companies who may have one instance of 0 and one instance of 1. I want to retrieve only companies where ALL records have 0 in the field 2 column.

 

[gmmastros]

Try this... The @Temp stuff is a table variable so I could dummy up some data.

Declare @Temp Table(CompanyName VarChar(50), Field2 Integer)

Insert Into @Temp Values('ABC Company',1)
Insert Into @Temp Values('ABC Company',1)
Insert Into @Temp Values('ABC Company',0)
Insert Into @Temp Values('DEF Company',0)
Insert Into @Temp Values('DEF Company',0)
Insert Into @Temp Values('DEF Company',0)

Select 	CompanyName, 
		Count(1) As RecordCount, 
		Sum(Case When Field2 = 0 Then 1 Else 0 End) As RecordsWith0
From 	@Temp 
Group By CompanyName
Having  Count(1) = Sum(Case When Field2 = 0 Then 1 Else 0 End)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Okay, you can also use not exists

Declare @Temp Table(CompanyName VarChar(50), Field2 Integer)

Insert Into @Temp Values('ABC Company',1)
Insert Into @Temp Values('ABC Company',1)
Insert Into @Temp Values('ABC Company',0)
Insert Into @Temp Values('DEF Company',0)
Insert Into @Temp Values('DEF Company',0)
Insert Into @Temp Values('DEF Company',0)

Select     * from @Temp t
where  Field2 =0 and not exists (select * from @Temp where Field2 =1 and t.CompanyName =CompanyName)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

how about???

select companyname
from @temp
group by companyname
having sum(field2) = 0

Jim

 

[gmmastros]

Jim, since the OP didn't specify whether there could be negative numbers, I didn't go in that direction. However...

select companyname
from @temp
group by companyname
having sum([!]Abs([/!]field2[!])[/!]) = 0

You're solution probably makes the most sense.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Cloonalt]

Sorry, Field2 is text so sum won't work.

It's actually 'I' and 'O'

 

[gmmastros]

In that case... Back to Case...When

Declare @Temp Table(CompanyName VarChar(50), Field2 VarChar(1))

Insert Into @Temp Values('ABC Company','I')
Insert Into @Temp Values('ABC Company','I')
Insert Into @Temp Values('ABC Company','O')
Insert Into @Temp Values('DEF Company','O')
Insert Into @Temp Values('DEF Company','O')
Insert Into @Temp Values('DEF Company','O')

Select 	CompanyName, 
From 	@Temp 
Group By CompanyName
Having  Count(1) = Sum(Case When Field2 = 'O' Then 1 Else 0 End)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Well since it is not 1 and 0, use George's or Denis's solution.

 

[SQLDenis]

why not ;-)

Declare @Temp Table(CompanyName VarChar(50), Field2 VarChar(1))

Insert Into @Temp Values('ABC Company','I')
Insert Into @Temp Values('ABC Company','I')
Insert Into @Temp Values('ABC Company','O')
Insert Into @Temp Values('DEF Company','O')
Insert Into @Temp Values('DEF Company','O')
Insert Into @Temp Values('DEF Company','O')

select companyname
from @temp
group by companyname
having sum(convert(int,replace(replace(field2,'I','1'),'O','0'))) = 0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

i prefer

having count(*) 
     = count(case when field2 = 'O' 
                  then 937 end)

r937.com | rudy.ca

 

[gmmastros]

Or....

Having Count(1) = Sum(Ascii(Field2)-78)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

;-)
or
Having Count(1) = Sum(patindex('O',Field2))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Or...

Having Count(1) = Count(NullIf(Field2, 'I'))



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Interesting, same result....
Having Count(1) = sum(ascii(Field2))/78
Having Count(1) = sum(ascii(Field2)/78)
Having Count(1) = sum(ascii(Field2)-78)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

george, that's very nice

and i must say, this is a most interesting thread

is there any difference here between count(*) and count(1)? ;-)

r937.com | rudy.ca

 

[SQLDenis]

doesn't matter, try this for fun

Having Count(23561215423165451564653156465313545) = Count(NullIf(Field2, 'I'))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

or this
Having Count(-343*244) = Count(NullIf(Field2, 'I'))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/