CODEWITHSUNDEEP
sqlsql-server
Chen
Chen

Reputation: 1

SQL Server : count pairs from two different columns

I have a query I need for an application I'm building, but I'm struggling to find the best answer.

Suppose I have a table named Messages, with the following columns:

MessageID, From, To, Text

I want to count all messages between From and To such as that if the table looks like this:

ID From  To   Text
------------------
 1    a   b     hi
 2    b   a  hello
 3    a   c   test

So basically I want the result to look like 

a, b, 2
a, c, 1

Appreciate your help

Upvotes: 0

Views: 153

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Most databases have least() and greatest(), which make this easier. In SQL Server, you can use case:

select (case when [from] < [to] then [from] else [to] end),
       (case when [from] < [to] then [to] else [from] end),
       count(*)
from t
group by (case when [from] < [to] then [from] else [to] end),
         (case when [from] < [to] then [to] else [from] end);

I would have named the columns, but I can't think of appropriate names.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

Similar answer to Gordon, posted about 20 seconds later, with one important difference. ANSI SQL requires that no derived columns appear in the GROUP BY clause. I first use a CTE to compute the least and greatest of the to/from pairs, and then do an aggregation query on that CTE. In this case, we end up grouping on actual, not derived, columns.

WITH cte AS (
    SELECT
        CASE WHEN [From] <  [To] THEN [From] ELSE [To] END AS [From],
        CASE WHEN [From] >= [To] THEN [From] ELSE [To] END AS [To]
    FROM yourTable
)

SELECT
    [From], [To], COUNT(*) AS cnt
FROM cte
GROUP BY
    [From], [To]

Note also that you should avoid using SQL keywords such as From to name your columns and tables. To get this query to work, you absolutely would have to use the column as [From], because without brackets SQL Server would confound it with the FROM keyword.

Upvotes: 5

Related Questions