SQL Pair Query?

I have only one table which contains 3 columns for travel company. It shows buses that went from City A to City B etc. I would like to find how many times this route has been used. I can easily find one way from A to B but I want this program to sum automatically from B to A in the same row.

In this scenario, `A` to `B` is equivalent to `B` to `A`. The requirement is to obtain the COUNT of ((`X` to `Y`) + (`Y` to `X`)) for any two arbitrary (and distinct) points `X`and `Y`).

Example Table

``````ID | FROM | TO
1  |  A   | B
2  |  C   | D
3  |  B   | A
4  |  C   | A
5  |  D   | C
``````

``````Route AB = 2
Route CD = 2
Route CA = 1
``````

etc.

For anyone wanting to help, here is the data in script form for easy copy/paste:

``````CREATE TABLE Routes
( ID INT NOT NULL,
ORIGIN VARCHAR(2) NOT NULL,
DESTINATION VARCHAR(2) NOT NULL
);

INSERT INTO Routes
( ID, ORIGIN, DESTINATION )
VALUES
( 1, 'A', 'B' ),
( 2, 'C', 'D' ),
( 3, 'B', 'A' ),
( 4, 'C', 'A' ),
( 5, 'D', 'C' ) ;

SELECT ID,
ORIGIN,
DESTINATION
FROM Routes;

DROP TABLE Routes;
``````
• Which DBMS are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... Jun 3, 2018 at 20:03

3 solutions (2 of which are similar to @stickybit's, but easier on the eye) are below.

I often find it beneficial to look at answers/threads which have multiple solutions to the problem - some of which are obviously better than others but it can be a learning experience!

The simplest and by far the most elegant solution is (thanks to the hint from @ypercube(tm)) is:

``````SELECT
LEAST(origin, destination) AS point_1,
GREATEST(origin, destination) AS point_2,
COUNT(*) AS journey_count
FROM route
GROUP BY point_1, point_2
ORDER BY point_1, point_2;
``````

Result (same for all solutions):

``````point_1, point_2, journey_count
A        B              2
A        C              1
C        D              2
``````

The fiddle for this is here. All the examples here use PostgreSQL 10, but any mainstream RDBMS should work(*) - maybe with some tweaks!

• (*)
• SQLite/SQL Server don't have the `LEAST()` or `GREATEST()` functions.
• Be careful with cases of identifiers for some systems
• The fiddles can be buggy for some servers!

The next fiddle here uses PostgreSQL 10 (for MySQL, version >= 8.0 is required for the CTE). Running this fiddle on MySQL will give extra data because of the `CHECK CONSTRAINT` I put in, see below. Incredibly, MySQL still doesn't have them! MariaDB does implement `CHECK`s.

``````SELECT point_1, point_2, count(*)
FROM
(
SELECT
CASE
WHEN origin < destination THEN origin ELSE destination
END AS point_1,
CASE
WHEN destination > origin THEN destination ELSE origin
END as point_2
FROM
routes
) AS tab
GROUP BY point_1, point_2
ORDER BY point_1, point_2;
``````

This subquery eliminates the need for the repeated `CASE`statement in @stickybit's solution.

Or, a CTE (Common Table Function - also available [here]https://dbfiddle.uk/?rdbms=postgres_10&fiddle=734ef45d84f5fb9cbba84cd1714318df)) can be used to the same end. For longer, more complex queries, this might be the way to go - CTE's are a godsend!

``````WITH the_route AS
(
SELECT
CASE
WHEN origin < destination THEN origin ELSE destination
END AS point_1,
CASE
WHEN destination > origin THEN destination ELSE origin
END as point_2
FROM
routes
)
SELECT point_1, point_2, COUNT(*)
FROM
the_route
GROUP BY point_1, point_2
ORDER BY point_1, point_2;
``````

As a final point (pardon the pun!), you might want to add a `CHECK CONSTRAINT` to your table definition by ensuring that origin and destination are never the same as follows:

``````CREATE TABLE Routes
(
route_id    INTEGER NOT NULL,
origin      VARCHAR(2) NOT NULL,
destination VARCHAR(2) NOT NULL,
-- CHECK (destination != origin) - can do it this way (remove -- comment)
CONSTRAINT routes_orig_dest_distinct_ck CHECK (destination != origin)
-- Better as it gives a meaningful name to the CONSTRAINT
-- You can check this by swapping the CONSTRAINTs
);
``````

You need to sort origin and destination first, to make the pairs with the same end points equal. As it's only two columns, you can do that in a `CASE ... END` here. Then you can `GROUP BY` these and get the `count(*)`.

`````` SELECT CASE
WHEN origin <= destination
THEN origin
ELSE
destination
END,
CASE
WHEN destination >= origin
THEN destination
ELSE
origin
END,
count(*)
FROM routes
GROUP BY CASE
WHEN origin <= destination
THEN origin
ELSE
destination
END,
CASE
WHEN destination >= origin
THEN destination
ELSE
origin
END;
``````

Barely STD SQL solution:

``````SELECT Route, COUNT(*) as Cnt
FROM
(SELECT CASE
WHEN (t1.Origin < t1.Destination) OR t2.Origin IS NULL THEN 'Route ' + t1.Origin + t1.Destination
ELSE 'Route ' + t1.Destination + t1.Origin
END as Route
FROM Routes t1
LEFT JOIN Routes t2
ON   t2.Origin = t1.Destination
AND  t2.Destination = t1.Origin
) t1
GROUP BY Route
ORDER BY Route;
``````
```Route    | Cnt
:------- | --:
Route AB |   2
Route CA |   1
Route CD |   2
```

db<>fiddle here

• I assume that the left join is to allow the single count route to be shown as `CA` instead of `AC`. Not entirely sure that's a requirement, but it is how the OP showed the results. Jun 4, 2018 at 16:16

Suggestion for SQL Server using `values` clause of `select` to mimic `greatest()` & `least()` functions:

``````--Rationalize to present equivalent origins and destinations together.
with Rationalization ( Origin, Destination )

as (

select

--Determine the minimum Origin value. (Equivalent to Oracle Least).
(  select   min( Element )      from    ( values    ( Origin ),
( Destination ) ) as List( Element ) ) as Origin,

--Determine the maximum Destination value. (Equivalent to Oracle Greatest).
(   select  max( Element )
from  ( values    ( Destination ),
( Origin ) ) as List( Element ) ) as Destination

from Routes )

select  count(0) as TotalJourneys,
Origin,
Destination
from    Rationalization
group by
Origin,
Destination
``````

Here is a very simple solution that provides the desired result for your data ;-)

Don't use it, it is wrong as a general solution. Can you see why? Can you improve on it? If you provide more details on what your real data looks like, we might get something along these lines that works for you.

I think is is worth mentioning as an answer here, as it demonstrates that grouping by columns not in the select list can sometimes be used to solve tricky SQL challenges in elegant ways...

``````SELECT  MIN(ORIGIN) AS Point1,
MAX(DESTINATION) AS Point2,
COUNT(*) AS NumRoutes
FROM Routes
GROUP BY ASCII([DESTINATION]) * ASCII([ORIGIN]);
``````
• This would only be valid if the ASCII codes for all characters are prime numbers. As @ypercube's answer demonstrates Jun 4, 2018 at 19:07