CODEWITHSUNDEEP
mysqlsqldatabase
Ander Gonzalez
Ander Gonzalez

Reputation: 27

SQL: How to select rows with a column value that appears at least 1 times?

I have a database like the following.

Database Example I want to get the account number and money of the accounts that have at least one holder (ID Card) of both sexes, that is, at least one male holder and at least one female holder.

This should be the result:

Result

This is the query I am using but it returns 0 rows. What could be the problem?

select accountNumber, money
from mytable
group by accountNumber, money
having min(sex) <> max(sex)

Thank you for helping

Upvotes: 0

Views: 486

Answers (3)

zealous
zealous

Reputation: 7503

You can do using count.

select
    accountNumber,
    Money
from yourTable
group by
    accountNumber,
    Money
having count(distinct sex) > 1

Upvotes: 0

GMB
GMB

Reputation: 222462

You can use aggregation, and filter with a having clause:

select accountNumber, money
from mytable
group by accountNumber, money
having min(sex) <> max(sex)

Condition min(sex) <> max(sex) is a simple and efficient way to ensure that both possible values are available for the given (accountNumber, money) tuple.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use aggregation and having:

select accountNumber, sum(money)
from t
group by accountNumber
having sum(sex = 'M') >= 1 and sum(sex = 'F') >= 1;

Upvotes: 0

Related Questions