# Sql-server – Grouping rows by looking at two columns without considering the order and summing each separately

group byMySQLsql server

I have a table looks like this one below:

+---------+---------+-------+
| SOURCE  | DEST    | VALUE |
+---------+---------+-------+
| A       | B       | 1     |
| B       | A       | 2     |
| A       | B       | 3     |
| C       | D       | 5     |
| D       | C       | 6     |
+---------+---------+-------+


So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+---------+---------+-------------+-----------+
|         |         | SUM(SOURCE) | SUM(DEST) |
+---------+---------+-------------+-----------+
| A       | B       | 4           | 2         |
| C       | D       | 5           | 6         |
+---------+---------+-------------+-----------+


Does anyone has any idea about how can I get this?

(I am trying to do this on both SQL Server, and MySQL)

It's not pretty but it works.

SELECT * INTO YourTable
FROM
(
SELECT 'A' AS [SOURCE],'B' AS DEST, 1 AS VALUE
UNION ALL
SELECT 'B','A',2
UNION ALL
SELECT 'A','B',3
UNION ALL
SELECT 'C','D',5
UNION ALL
SELECT 'D','C',6
) A;

WITH CTE
AS
(
SELECT  CASE
WHEN [Source] < Dest THEN [Source]
ELSE Dest
END col1,
CASE
WHEN [Source] < Dest THEN [Dest]
ELSE [Source]
END col2,
SUM(Value) AS [SUM(Total)]
FROM YourTable
GROUP BY
CASE WHEN [Source] < Dest THEN [Source] ELSE Dest END,
CASE WHEN [Source] < Dest THEN [Dest] ELSE [Source] END
)

SELECT  A.col1,
A.col2,
SUM(B.VALUE) AS [SUM(SOURCE)],
[SUM(Total)]-SUM(B.VALUE) AS [SUM(DEST)]
FROM CTE A
INNER JOIN YourTable B
ON A.[col1] = B.[SOURCE]
AND A.col2 = B.DEST
GROUP BY A.col1,A.col2,[SUM(Total)]

DROP TABLE YourTable


Results:

col1 col2 SUM(SOURCE) SUM(DEST)
---- ---- ----------- -----------
A    B    4           2
C    D    5           6