Summing the count of two columns in postgres that contain the same value

Question

I want to sum the total number of occurrences of one value across two columns in the same table. So, an example would be:

id |node1  |node2
1  |  111  |   123
2  |  122  |   124
3  |  111  |   125
4  |  122  |   111
5  |  124  |   111
6  |  126  |   111

So in this case I want to get the following result:

node   | node_count
111    |     5
122    |     2
123    |     1
124    |     2
125    |     1
126    |     1

Additionally, I want to only include the nodes that have a count > 1 so my final result would be:

node   | node_count
111    |     5
122    |     2
124    |     2

I didn't think this would work but I've tried the following on the table:

SELECT count(node1+node2), node1 as node 
FROM table1 
WHERE node1 = node2 
GROUP BY node1 
HAVING count(node1+node2) > 1;

So I then tried creating a temporary table so that I could use the WHERE clause as follows

SELECT count(table1.node1+tableTemp.node2), table1.node1 
FROM table1, tableTemp 
WHERE table1.node1 = tableTemp.node2 
GROUP BY table1.node1 
HAVING count(table1.node1+tableTemp.node2) > 1;

But this only seems to return the count of node1. I have also tried the variation of count(table1.node1) + count(tableTemp.node2) but this doesn't work. I've also tried using a combination of SUM and COUNT-sub-queries to no avail. Can anyone point me in the correct direction? Cheers.

Answer 1

This should be substantially cheaper than what we had so far:

SELECT x.node, count(*) AS node_count
FROM   tbl t, LATERAL (VALUES (t.node1), (t.node2)) AS x(node)
GROUP  BY 1
HAVING count(*) > 1;

db<>fiddle here

Only needs a single pass over the underlying table.
Adding ORDER BY is optional, but no sort order was requested.

Related:

• SELECT DISTINCT on multiple columns

Answer 2

You can get it converting the two columns into a single column.

SELECT node, COUNT(*) as node_count
FROM
(
    SELECT id, node1 as node
    FROM   t1
    UNION
    SELECT id, node2 as node
    FROM   t1
) t2
GROUP BY node
HAVING COUNT(*) > 1
ORDER BY node;

node | node_count
---: | ---------:
 111 |          5
 122 |          2
 124 |          2

db<>fiddle here