mgo mgo - 2 years ago 62
SQL Question

Identifying and marking single and multiple account holders in sql

I'm currently obsessed with this SQL issue. I have the following table

`account_id` `person` `account_type`

001 eric PH

001 lucy SH

002 stacy PH

002 Chris SH

002 Ruud SH

003 Angel PH

The table shows that there are people that share an
e.g. 001 and 002. The
column identified whether the person is the Primary or Secondary Holder of the account.

The people with
001 and 002 should get a 1 value in a new column named

If the person is a single account holder, the
value should be 0.

Expected output


001 eric PH 1

001 lucy SH 1

002 stacy PH 1

002 Chris SH 1

002 Ruud SH 1

003 Angel PH 0

How would you recommend to solve this in SQL? I am using SQL server 2012

Thanks for your response, best regards.

Answer Source

It sounds like you just want to compute which accounts have been assigned to more than one person. If so, the following UPDATE statement should do the trick:

SET t1.multi-account = CASE WHEN t2.personCount > 1 THEN 1 ELSE 0 END 
FROM accounts AS t1
    SELECT account_id, COUNT(DISTINCT person) AS personCount
    FROM accounts
    GROUP BY account_id
) AS t2
    ON t1.account_id = t2.account_id

This answer assumes that you already have a column called multi-account in your table. If not, then create it via:

ALTER TABLE accounts ADD multi-account BIT
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download