CODEWITHSUNDEEP
sql-serverselect
monkey
monkey

Reputation: 19

Simple query in MSSQL. SELECT

I'm an elementary user of MSSQL. I cannot finish my query properly. It should be a simple problem.

Here is my code:

SELECT Pass_in_trip.date, Count(date) as numbOfPassengers
From Pass_in_trip, Trip
WHERE (Trip.trip_no = Pass_in_trip.trip_no AND Trip.town_from='Rostov')
Group BY date
HAVING COUNT(date) = 3

This way it does work. But I want to compare COUNT(date) with MAX(COUNT(date)) and then I get a problem:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Upvotes: 0

Views: 56

Answers (1)

Phil Pledger
Phil Pledger

Reputation: 476

I think this may be what you are looking for.

A common table expression will help avoid having to repeat the query.

WITH cte as
  (
    SELECT Pass_in_trip.date, Count(date) as numbOfPassengers
    FROM Pass_in_trip
    INNER JOIN Trip 
       ON Trip.trip_no = Pass_in_trip.trip_no
    WHERE Trip.town_from='Rostov'
    GROUP BY date
  )
SELECT date, numbOfPassengers
FROM cte
WHERE numbOfPassengers = (SELECT MAX(numbOfPassengers) FROM cte)

Upvotes: 1

Related Questions