Derek Derek - 5 months ago 11
SQL Question

query postgres for distinction across two columns

i have a database with the following table

Col1 | Col2 | Col3 |
A | C | data1
A | B | data2
B | A | data3
A | D | data4
C | A | data5


I need a query that will select all distinct rows across Col1 and Col2 (So AC == CA), but I need to count the total.

So my return might be like

Combo| Count
AC | 2
AB | 2
AD | 1

Answer

This looks like a simple aggregation query with a twist because the ordering is not important:

select least(col1, col2) || greatest(col1, col2), count(*) as cnt
from t
group by least(col1, col2) || greatest(col1, col2);