Grouping rows by looking at two columns without considering the order and summing each separately

Question

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)

Related: Grouping rows by looking at two columns without considering the order

Answer 1

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

Answer 2

SELECT  LEAST(source, dest),
        GREATEST(source, dest),
        SUM(IF(source < dest, value, 0)),
        SUM(IF(source > dest, value, 0))
    FROM  tbl
    GROUP BY  1,2;