Find top 2 cities visitor goes

Question

The objective is to find the 2 most popular cities travelers go to. End result is to see which 2 cities has the highest count.

Is it shanghai + beijing or chengdu + shenyang, etc.

I'm stuck after coming down to the below dataset.

3 columns below. ID = customerid, city = cities customers traveled, row num = total cities by each traveler.

ID     | City    | row_num
------ | ------  | ------
177    | DALIAN  | 1
177    | SHANGHAI| 2
177    | SHENYANG| 3
291    | BEIJING | 1
291    | DALIAN  | 2
5045   | CHENGDU | 1
5045   | CHONGQIN| 2
5045   | GUANGZHO| 3
5195   | XI'AN   | 1
5195   | XIAN    | 2

select ID, City, row_num from [table1] group by ID, City, row_num

Stuck at the above table (simplified). But ultimately i want to show below:

Cities           | Occurrence
------           | ------   
shanghai+beijing | 550
------           | ------   
beijing+chengdu  | 320
------           | ------   
chengdu+shengyang| 110

Using SQL Server btw.

Answer 1

Try this:

SELECT TOP 2 X.City
FROM (
       SELECT City,COUNT(City) as CN
       FROM [YourTable]
       GROUP BY City
                       ) X
ORDER BY X.CN DESC