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)

Best Answer

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

Related Solutions

Mysql – Want to order on two columns but not the usual way

SELECT *
FROM Mytable
ORDER BY
   userID, Date

I assume Date is really a date/time type and not varchar...

Edit, after clarification:

Untested

SELECT
    M.*
FROM
    ( --one row for each user
    SELECT MIN(Date) AS FirstUserDate, userID
    FROM MyTable
    GROUP BY userID
    ) foo
    JOIN
    MyTable M ON foo.userID = M.userID
ORDER BY
    foo.FirstUserDate, M.userID, M.Date;

SQL Server – Grouping Rows by Two Columns Without Considering Order

Something like:

SELECT COUNT(*) AS C, V1, V2
FROM   (SELECT CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
             , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
        FROM   input_table
       ) AS tbl
GROUP BY V1, V2

should do the trick, but may not be terribly efficient. Any filtering clauses should be added to the inner select, not the outer.

SELECT COUNT(*) AS C
     , CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
     , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
FROM   input_table
GROUP BY CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END, CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END

may also work (and may be more efficient) but the code repetition between the GROUP and SELECT clauses may become a maintenance problem.

Of course if you can ensure that the data is always the "right way around" (and this doesn't break your model in other ways - we can't tell if it might as your questions gives no detail on which to base a supposition either way) then

SELECT COUNT(*) AS C, Value1, Value2
FROM   input_table
GROUP BY Value1, Value2

is sufficient. You could enforce the two values being the right way around using INSTEAD OF triggers or in your business logic layer (updating existing data should be easy).

Best Answer

Related Solutions

Mysql – Want to order on two columns but not the usual way

SQL Server – Grouping Rows by Two Columns Without Considering Order

Related Question