How to "cross group" in a microsoft sql query?

user_16252202 0
Viewed 44

I have a table. There are 2 columns. "Source" and "Destination"

Source Destination
ABC DEF
DEF ABC

Now there are 2 results when query them by SELECT Source, Destinaton FROM dbo.Pakete GROUP BY Source, Destinaton;
But I want them to count as one. Like a cross combination from ABC and DEF which exist 2 times!

Example:

Source Destination Count
ABC DEF 2

Is there any query solution in Microsoft SQL Query Language?

Thanks!
Severin!

count group-by sql-server sql
2 Answers

You can use case expressions to aggregate by the smallest and biggest values:

SELECT (CASE WHEN Source < Destination THEN SOURCE ELSE Destination END),
       (CASE WHEN Source > Destination THEN SOURCE ELSE Destination END),
       COUNT(*)       
FROM dbo.Pakete p
GROUP BY (CASE WHEN Source < Destination THEN SOURCE ELSE Destination END),
         (CASE WHEN Source > Destination THEN SOURCE ELSE Destination END)
user_11440350

You can sort source and destination to make sure that source is always lesser than destination. Now, you can apply group by.

declare @table table( Source    char(3) , Destination char(3))

insert into @table values
('ABC','DEF')
,('DEF','ABC');

SELECT source, destination, count(*) as cnt
from
(
select case when source < Destination then source else Destination end as source,
       case when source < Destination then destination else source end as destination
from @table
) as modifiedSrcDest
group by source, destination
source destination cnt
ABC DEF 2
user_6349350
Related