question

yaj avatar image
yaj asked

Count of combinations

I have the following table id Departure Arrival 1 Mumbai Delhi 2 Delhi Mumbai 3 Delhi Chennai 4 Chennai Delhi Desired output Departure Arrival Count Mumbai Delhi 2 Delhi Chennai 2 Note: I want combination of Mumbai-Delhi or Delhi-Mumbai as same
querycombinationscounts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
There are probably other/better solutions, but I Think this will work. create table flights(id int primary key, departure varchar(50), arrival varchar(50)); insert flights (id,departure,arrival) values ( 1, 'Mumbai', 'Delhi'), (2, 'Delhi', 'Mumbai'), (3, 'Delhi', 'Chennai'), (4, 'Chennai', 'Delhi'), (5,'Delhi','Mumbai'); with cte as ( select id,departure,arrival from flights union all select id,arrival,departure from flights ),cte2 as ( select *, row_number() over(partition by id order by departure) as rownum from cte )select departure,arrival,count(*) from cte2 where rownum=1 group by departure,arrival An alternative solution, not using ranking functions: with cte as ( select id,departure,arrival from flights union all select id,arrival,departure from flights ),cte2 as ( select distinct id from cte ),cte3 as ( select c.id,t.departure,t.arrival from cte2 c cross apply (select top 1 departure,arrival from cte where cte.id= c.id order by departure) t )select departure,arrival,count(*) from cte3 group by departure,arrival
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yaj avatar image yaj commented ·
But here i do not want to use partition by technique so is there any other way out??????????
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
There are certainly other ways, but this is the way I intuitively thought of. Why do you not want to use ranking functions to solve your problem?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.