mgo mgo - 7 months ago 11
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
account_id
e.g. 001 and 002. The
account_type
column identified whether the person is the Primary or Secondary Holder of the account.

The people with
account_id
001 and 002 should get a 1 value in a new column named
multi-account
.

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

Expected output

account_id
person
account_type
multi-account


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

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:

UPDATE t1
SET t1.multi-account = CASE WHEN t2.personCount > 1 THEN 1 ELSE 0 END 
FROM accounts AS t1
INNER JOIN
(
    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