CODEWITHSUNDEEP
mysqlsql
emiya
emiya

Reputation: 103

SQL: Query group depending on the number of values appear in column in a group

I have a table of people:

id | group| age
----------------
1  | 1    | old
2  | 1    | young
3  | 1    | old
4  | 2    | old
5  | 2    | young
6  | 2    | young
7  | 3    | old
8  | 3    | old
9  | 3    | old
10 | 4    | young
11 | 4    | young
12 | 4    | old

Now I need to query to get the info of a random 1 group which satisfies that the number of young is 2, and the number of old is 1 (group 2 and 4 satisfies what I said), so like below:

id | group| age
----------------
4  | 2    | old
5  | 2    | young
6  | 2    | young

or

id | group| age
----------------
10 | 4    | young
11 | 4    | young
12 | 4    | old

How can you do that ?

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

How about using aggregation to define the group you want?

select group
from t
group by group
having sum(age = 'young') = 2 and sum(age = 'old') = 1;

Then, if you want the original rows for a random such group, you can join back:

select t.*
from t join
     (select group
      from t
      group by group
      having sum(age = 'young') = 2 and sum(age = 'old') = 1
      order by rand()
      limit 1
     ) g
     on g.group = t.group;

Note that group is a very poor name for a column because it is a SQL keyword. I hope you are using better naming conventions in your real tables.

Upvotes: 1

Related Questions