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

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.

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

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: