Help: Wrong count in query

Transact-SQL
, , , ,
1

Find out the number of routes with the greatest number of flights (trips).
Notes.

  1. A - B and B - A are to be considered the SAME route.
  2. Use the Trip table only.

Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in).

I have done this query :
SELECT COUNT(*) AS QTY FROM (
Select town_from , count(distinct trip_no) NR
FROM TRIP
GROUP BY town_from
HAVING count(distinct trip_no) = (
SELECT MAX(NR) FROM (
SELECT town_from , count(distinct trip_no) NR
FROM TRIP
GROUP BY town_from ) a ) ) c

AND THE RESULT I GET IS (1).
THE CORRECT RESULT IS (2) , any help ?

2

Dont know how to select them as a same route , bcs if i make a where t.from=t.to it appears me 0

3

please provide sample data for trip as follows

--DDL

declare @trip table(trip_no int, id_comp int, plane int, 
town_from int, town_to int, time_out in, time_in int). 
---I gave them all int but please provide the correct data type for columns

Next comes DML

insert into @trip
select 1, 2,3,4,5,6,7 union
select 8,9,10,11,12,13

but of course you provide real/near to real data to help us help you

4

I work on this site https://www.sql-ex.ru/learn_exercises.php#answer_ref
And there is no DML just appears the correct result , so i dont know what to put on these dml
Just want to fix my query. Sorry and thank u for ur time

5
  1. A - B and B - A are to be considered the SAME route.
    2.- scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;
6

DDL

Summary

Create table #Trip(trip_no int ,
ID_comp int,
plane varchar(10),
town_from varchar(25),
town_to varchar(25),
time_out datetime,
time_in datetime)

Sample Data

Summary

insert into #trip values
(1100,4,'Boeing','Rostov','Paris','1900-01-01 14:30:00.000','1900-01-01 17:50:00.000')
,(1101,4,'Boeing','Paris','Rostov','1900-01-01 08:12:00.000','1900-01-01 11:45:00.000')
,(1123,3,'TU-154','Rostov','Vladivostok','1900-01-01 16:20:00.000','1900-01-01 03:40:00.000')
,(1124,3,'TU-154','Vladivostok','Rostov','1900-01-01 09:00:00.000','1900-01-01 19:50:00.000')
,(1145,2,'IL-86','Moscow','Rostov','1900-01-01 09:35:00.000','1900-01-01 11:23:00.000')
,(1146,2,'IL-86','Rostov','Moscow','1900-01-01 17:55:00.000','1900-01-01 20:01:00.000')
,(1181,1,'TU-134','Rostov','Moscow','1900-01-01 06:12:00.000','1900-01-01 08:01:00.000')
,(1182,1,'TU-134','Moscow','Rostov','1900-01-01 12:35:00.000','1900-01-01 14:30:00.000')
,(1187,1,'TU-134','Rostov','Moscow','1900-01-01 15:42:00.000','1900-01-01 17:39:00.000')
,(1188,1,'TU-134','Moscow','Rostov','1900-01-01 22:50:00.000','1900-01-01 00:48:00.000')
,(1195,1,'TU-154','Rostov','Moscow','1900-01-01 23:30:00.000','1900-01-01 01:11:00.000')
,(1196,1,'TU-154','Moscow','Rostov','1900-01-01 04:00:00.000','1900-01-01 05:45:00.000')
,(7771,5,'Boeing','London','Singapore','1900-01-01 01:00:00.000','1900-01-01 11:00:00.000')
,(7772,5,'Boeing','Singapore','London','1900-01-01 12:00:00.000','1900-01-01 02:00:00.000')
,(7773,5,'Boeing','London','Singapore','1900-01-01 03:00:00.000','1900-01-01 13:00:00.000')
,(7774,5,'Boeing','Singapore','London','1900-01-01 14:00:00.000','1900-01-01 06:00:00.000')
,(7775,5,'Boeing','London','Singapore','1900-01-01 09:00:00.000','1900-01-01 20:00:00.000')
,(7776,5,'Boeing','Singapore','London','1900-01-01 18:00:00.000','1900-01-01 08:00:00.000')
,(7777,5,'Boeing','London','Singapore','1900-01-01 18:00:00.000','1900-01-01 06:00:00.000')
,(7778,5,'Boeing','Singapore','London','1900-01-01 22:00:00.000','1900-01-01 12:00:00.000')
,(8881,5,'Boeing','London','Paris','1900-01-01 03:00:00.000','1900-01-01 04:00:00.000')
,(8882,5,'Boeing','Paris','London','1900-01-01 22:00:00.000','1900-01-01 23:00:00.000')

try this

;with cte as (Select town_from, town_to, count(1) as NumTrips
  from #Trip
group by town_from, town_to)


Select c.Town_from, c.Town_to, C.NumTrips + c1.NumTrips 
  from cte c
	left join cte c1
		on c.Town_From = c1.Town_to
		and c.Town_To = c1.Town_From
7

Hello Mike hope u good
The result of you query its
Your query:

correct query:
Town_from Town_to
London Paris 2
London Singapore 8
Moscow Rostov 8
Paris London 2
Paris Rostov 2
Rostov Moscow 8
Rostov Paris 2
Rostov Vladivostok 2
Singapore London 8
Vladivostok Rostov 2

8

Forgot the order by

with cte as (Select town_from, town_to, count(1) as NumTrips
  from #Trip
group by town_from, town_to)


Select c.Town_from, c.Town_to, C.NumTrips + c1.NumTrips 
  from cte c
	left join cte c1
		on c.Town_From = c1.Town_to
		and c.Town_To = c1.Town_From
  order by 3 desc
1 Like
9

Thanks for ur time mike , appreciate it :slight_smile: .