CODEWITHSUNDEEP
sqlsql-serverduplicatesdistinctsql-server-2014
rahul yadav
rahul yadav

Reputation: 3

SQL Server query returns duplicate results because of multiple group by columns

Here is my query : 

select
    Booking.BookingID, Booking.BookingNo, 
    Booking.Vcode, Booking.TransID, 
    Booking.StatusID,Booking.BookingDate,
    BookingContact.FirstName, BookingContact.MiddleName, BookingContact.LastName, 
    Schedule.DepatureDateTime as DepartDate, 
    Origin.City as Origin, Destination.City as Destination, 
    PaxJourney.TripTypeID
from 
    Booking
inner join 
    Pax on Booking.BookingID = Pax.BookingID  
inner join 
    PaxJourney on Pax.PaxID = PaxJourney.PaxID 
inner join 
    Schedule on PaxJourney.ScheduleID = Schedule.ScheduleID
inner join 
    City as Origin on Schedule.DepartureCityID = Origin.CityID
inner join 
    City as Destination on Schedule.ArrivalCityID = Destination.CityID
inner join 
    BookingContact on Booking.BookingID = BookingContact.BookingID
group by 
    Booking.BookingID ,Booking.BookingNo, Booking.Vcode, Booking.TransID, 
    Booking.StatusID,Booking.BookingDate, BookingContact.FirstName, 
    BookingContact.MiddleName, BookingContact.LastName, 
    Schedule.DepatureDateTime, Origin.City, Destination.City, PaxJourney.TripTypeID

Question: how to remove multiple group by fields in my query?

I got duplicate data because of these two "Origin.City, Destination.City" columns when I remove any one column in group by section I got error :

Msg 8120, Level 16, State 1, Line 22
Column 'City.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I want to use group by with one column like "group by Booking.BookingID"

Upvotes: 0

Views: 76

Answers (1)

Ionic
Ionic

Reputation: 3935

Well you need to use a aggregation function, as the error stated. All columns you won't group, must use an aggregation.

If you have only a group by based on Booking.BookingId you need to use an aggregation function on all other columns you select. For example:

SELECT Booking.BookingId, MAX(City.City) as City
FROM Booking
GROUP BY Booking.BookingId

Even if all rows of City.City show the same value, SQL Server won't execute your query, as it may occur a duplicate value with different contents. This way you need to define the proper aggregation (e.g. MIN,MAX,AVG, etc.) on all not grouped columns.

Upvotes: 2

Related Questions