Katalo Katalo - 1 month ago 7
SQL Question

How to count or group relations from two colums with SQL

I know the basics of SQL but is not more than a beginner, so I have no idea how to search for this question the right way.

Question:
How do I count matching relations in a two column set up? Example:

I use:

SELECT "from", "to", COUNT(*) Count
FROM "LocationDestination"
GROUP BY "from", "to"
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


which get me:

from to Count
Germany USA 6
USA Spain 5
Marocco Spain 4
USA Germany 2
Spain Marocco 2


What I want is a table that lookes like this:

Destination1 Destionation2 Count
Germany USA 8
Marocco Spain 6
USA Spain 5


Thus combining the trips Germany - USA with USA - Germany, and Marocco - Spain with Spain - Marocco, and so forth..

How can that be achieved?

Answer

Here is another way

SELECT case when froms > tos then  froms else tos end,
        case when froms < tos then froms else tos end,
        Count(*)
FROM   Yourtable
GROUP  BY case when froms > tos then  froms else tos end,
        case when froms < tos then froms else tos end
Comments