Home > Mobile >  PostgreSQL : Group By with two columns
PostgreSQL : Group By with two columns

Time:04-21

I'm using Postgresql. I have table A with this sample data:

ID NAME HOME_PHONE HAND_PHONE
1 Peter 0901111111 0811111111
2 Peter null 0901111111
3 Peter 0811111111 null
4 Marry 0922222222 0822222222
5 Marry 0933333333 0922222222
6 Jack 0912345678 null
7 Bob 0944444444 null
8 Bob null 0944444444
9 Han 0955555555 null
10 Han 0955555555 null

I need to get data with has same NAME and same HOME_PHONE, HAND_PHONE

Expected result: Peter, Marry, Bob, Han

  1. Peter has same Phone: 0901111111 or 0811111111 -> hit
  2. Marry has same Phone: 0922222222 -> hit
  3. Bob has same Phone: 0944444444 -> hit
  4. Han has same Phone: 0955555555 -> hit

How to query with using Group By, Having, etc....?

CodePudding user response:

You can try to use EXISTS subquery with your logic, if you want to remove duplicate name that you can use DISTINCT

SELECT DISTINCT name 
FROM A t1 
WHERE EXISTS (
 SELECT 1 
 FROM A tt
 WHERE t1.NAME = tt.NAME
 AND (
    t1.HOME_PHONE = tt.HAND_PHONE
 OR 
    t1.HAND_PHONE = tt.HOME_PHONE
 OR
     t1.HAND_PHONE = tt.HAND_PHONE
 OR 
    t1.HOME_PHONE = tt.HOME_PHONE
 ) AND t1.id <> tt.id
)

sqlfiddle

If you want to get the name with a comma in one row you can try to use string_agg function

SELECT string_agg(DISTINCT name,',') 
FROM A t1 
WHERE EXISTS (
SELECT 1 
 FROM A tt
 WHERE t1.NAME = tt.NAME
 AND (
    t1.HOME_PHONE = tt.HAND_PHONE
 OR 
    t1.HAND_PHONE = tt.HOME_PHONE
 OR
     t1.HAND_PHONE = tt.HAND_PHONE
 OR 
    t1.HOME_PHONE = tt.HOME_PHONE
 ) AND t1.id <> tt.id
)

CodePudding user response:

You can try with a SELF JOIN:

SELECT DISTINCT t1.name
FROM       tab t1
INNER JOIN tab t2
        ON t1.NAME = t2.NAME
       AND (t1.HOME_PHONE = t2.HAND_PHONE 
         OR t1.HOME_PHONE = t2.HOME_PHONE
         OR t1.HAND_PHONE = t2.HAND_PHONE)

Here's the SQL Fiddle: https://www.db-fiddle.com/f/s8RQa8Nptg4cmGEVjQHoUE/3.

  • Related