How to join two columns of the same table and count the number of rows

Question

Need help with the below scenario in PostgreSQL.

I need to match column A & column B and column B & column A in Table 1 and count the number of wires based on column A and column B.

Table 1

Wire	Device From (Column A)	Device To (Column B)	Level
Wire 1	Device 1	Device 2	level 1
Wire 2	Device 1	Device 2	level 1
Wire 3	Device 2	Device 1	level 1

Output should look like

No of Wire	Wires	Device from	Device To	Level
3	Wire 1, Wire 2, Wir3 3	Device 1	Device 2	level 1

bfris · Answer 1 · 2022-10-27T17:01:31.107

To solve your problem in a compact way, I converted your device_from and device_to columns to numbers (integers)

CREATE TABLE wiretable ( 
     wire text,
     device_from integer,
     device_to integer,
     level text);

INSERT INTO wiretable
         (wire, device_from, device_to, level)      
   VALUES('Wire 1', 1, 2, 'level 1'),
         ('Wire 2', 1, 2, 'level 1'), 
         ('Wire 3', 2, 1, 'level 1');

You need a way of comparing Device 1 and Device 2 so that 1,2 is the same as 2,1. The only way I could think of to do it was by using an array and sorting. But to do array sorting, you have to install the intarray extension (CREATE EXTENSION intarray;) or do some tricks. See this question for how to sort arrays and/or install extension.

Basically you want to do a GROUP BY on two columns. I couldn't find anything like that in the documentation. So I put both columns in an array and GROUP BY that array. I have to do it in two steps.

  SELECT count(*), string_agg(wire, ', '),
         sort(array[device_from, device_to]) as combo, 
         level 
    FROM wiretable 
GROUP BY combo, level;

count	string_agg	combo	level
3	Wire 1, Wire 2, Wire 3	{1,2}	level 1

That looks pretty good, but now we have to split combo back into two columns. We do this with a CTE

WITH foo AS (SELECT count(*), string_agg(wire, ', '),
                    sort(array[device_from, device_to]) as combo,  
                    level
               FROM wiretable
           GROUP BY combo, level)

SELECT foo.count, foo.string_agg as wires, 
       foo.combo[1] as "Device From",
       foo.combo[2] as "Device To", 
       foo.level
  FROM foo

which gives

count	wires	Device From	Device To	level
3	Wire 1, Wire 2, Wire 3	1	2	level 1

The problem is Wire3 has to be Device 2,Device 1 not Device 1,and Device 2. In this case the query is not working. I want to check device_from-->device_to and device_to-->device_to then combine the wires. — prathibha, Oct 27 '22 at 14:03
Oops. I didn't read your Question carefully enough. I have rewritten my answer to make it insensitive to Device From/Device To — bfris, Oct 27 '22 at 17:02
Sorry I am new to postgresql, this query is not working for me. ERROR: function sort(text[]) does not exist LINE 3: SORT(array[device_from, device_to]) as combo, ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 — prathibha, Oct 27 '22 at 19:10
You'll have to either install the `intarray` extension ('CREATE EXTENSION intarray;`) or follow the link in the answer for alternate ways to sort an array. — bfris, Oct 27 '22 at 19:26
Thank you, can this be achieved by a direct select statement as I can use this select statement in my JPA — prathibha, Oct 29 '22 at 16:35
Yes. Follow the link in the answer for alternate ways to sort an array. — bfris, Oct 29 '22 at 18:24

How to join two columns of the same table and count the number of rows

1 Answers1