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.