CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
s-a-n
s-a-n

Reputation: 787

Filter the data with GROUP BY in SQL Server

I have table with data something like this

Id    value1    value2  IsIncluded
----------------------------------
1859    1702    4043    0
1858    1706    4045    0
1858    1703    4046    1
1860    1701    4046    0
1861    1702    4047    0

To get the Ids with min(value1) and max(value2) and filter based on included column I can do something like this

select 
    Id, min(value1), max(value2)
from table
where IsIncluded = 0
group by Id

and I get the result

Id    value1    value2  IsIncluded
-----------------------------------
1859    1702    4043    0
1858    1706    4045    0
1860    1701    4046    0
1861    1702    4047    0`

but can I filter the data more if there is 1 in IsIncluded for that Id then it shouldn't pick up that row.

Upvotes: 0

Views: 681

Answers (5)

s-a-n
s-a-n

Reputation: 787

Similar to Gordon Linoff, this worked for me

select Id, min(value1), max(value2) from table group by Id having sum(case when IsIncluded=1 then 1 else 0 end)=0

Upvotes: 0

Hlin
Hlin

Reputation: 134

you could try this,

select ID,min(Value1),max(Value2) from table
where ID not in(select distinct ID from table where IsYpIncluded=1)
group by ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Hmmm. I'm thinking that you want to exclude any id that has a 1 in any row. If so, you can use a having clause:

select Id, min(value1), max(value2)
from table
group by Id
having max(cast(IsYpIncluded as int)) = 0;

This assumes that IsYpIncluded is never negative, but that seems to be the case based on the data in the question.

Upvotes: 0

user3568303
user3568303

Reputation: 102

select Id, min(value1), max(value2) from table where IsYpIncluded<0 group by Id

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

select Id, min(value1), max(value2)
from table t
where t.IsYpIncluded=0
  and not exists (
  select 0 from table t2
  where t.Id = t2.Id
  and t2.IsYpIncluded = 1
)
group by t.Id;

Upvotes: 2

Related Questions