2

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.

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

0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.