CODEWITHSUNDEEP
mysql
gh0strider18
gh0strider18

Reputation: 1140

SQL group by where values are same within group

So, I have a table with which individuals (person_id) have multiple lines (up to 4) and values for a column (value_column) can either = 0 or 1

I'd like to write a piece of code that returns a row for each person_id in which their value for value_column is only 0 or only 1 (even though they may have 3 or 4 rows each)

It's probably an easy line of code, but for someone with less SQL experience, it seems nearly impossible!

EDIT: here is a quick sample of lines:

person_id  value_column
A          0
A          1
A          0
B          0
B          0
B          0
B          0
C          1
C          1
C          1

And I would expect the line of code to return the folowing:

person_id value_column
B          0
C          1

Upvotes: 0

Views: 56

Answers (2)

Rahul
Rahul

Reputation: 77876

You can try something like this probably

select distinct * from table1
where person_id in
( select person_id
  from table1
  group by person_id
  having count(distinct value_column) <= 1
)

Inner query, will return only those person_id for which there is only one value_column present and that's the same thing getting done by count(distinct value_column) <= 1 and then outer query just selects everything for those person_id.

Upvotes: 2

Stephane BOISSEAU
Stephane BOISSEAU

Reputation: 86

select * from myTable where person_id not in 
(select a.person_id from myTable a, myTable b
  where a.person_id = b.person_id
    and a.value_column <> b.value_column)

Get persons with different values and then get those who are not in this first query.

Or quicker and nicer :

select person_id, min(value_column)
 from myTable
 group by person_id
 having min(value_column)=max(value_column)

Upvotes: 0

Related Questions