0

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

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
0
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;
  • How does this work with the value column? – Petah Mar 10 '15 at 20:25
  • See if the edit fixed it. – Rick James Mar 10 '15 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.