SQLTeam.com | Weblogs | Forums

Each GROUP BY expression must contain at least one column that is not an outer reference

Would be great if somebody could help me with the query I am trying to build below. Ideally I want to end up with a table with 7 columns: EstabName, Country, Province, EstablishmentID, bookingsCY, bookingsLY and Searches. I know im close; however as it stands I keep getting the error message: 'Each GROUP BY expression must contain at least one column that is not an outer reference' amongst others. Would appreciate it so much if someone could take the below script and tweak it for me to get it working.

Thanks so much

SELECT
bt.title as EstabName
,p.countryId as country
,p.provinceId as Province
,bt.MasterEstabId as EstablishmentId
,count(distinct(bt.bookingid)) as bookingsCY
,count(distinct(bt.bookingid)) as bookingsPY
,pr.ActiveSearch as Searches

FROM [dbo].[Booking_tbl] as bt

left join [dbo].[Booking_tbl] b on bt.BookingId= b.BookingId
left join [dbo].[acomEstab_tbl] ae on bt.MasterEstabId = ae.estabid
left join [dbo].[BookingMeta] p on bt.BookingId= p.BookingId
left join [dbo].[acomProvince_tbl] pr on p.ProvinceId= pr.ProvinceId and p.CountryId= pr.CountryId

where bt.BookedDate between '2019-04-01' and '2019-05-22'
and b.BookedDate between '2018-04-01' and '2018-05-22'
and ae.title is not null
and 'bookingsCY' = 0
group by 'bt.EstabName'
order by 1 asc, 2 asc, 6 desc

Here is something that may work. Since I don't know all the business rules, it may not be exactly what you need, but the query should run without errors.

SELECT bt.title AS EstabName,
       p.countryId AS country,
       p.provinceId AS Province,
       bt.MasterEstabId AS EstablishmentId,
       COUNT(DISTINCT (bt.bookingid)) AS bookingsCY,
       COUNT(DISTINCT (bt.bookingid)) AS bookingsPY,
       pr.ActiveSearch AS Searches
FROM [dbo].[Booking_tbl] AS bt
    LEFT JOIN [dbo].[Booking_tbl] b
        ON bt.BookingId = b.BookingId
		  AND b.BookedDate
		  BETWEEN '2018-04-01' AND '2018-05-22'
		  -- the above moved to the join. If you do that in where clause,
		  -- the left join will effectively become an inner join.
    LEFT JOIN [dbo].[acomEstab_tbl] ae
        ON bt.MasterEstabId = ae.estabid
		  AND ae.title IS NOT NULL
    LEFT JOIN [dbo].[BookingMeta] p
        ON bt.BookingId = p.BookingId
    LEFT JOIN [dbo].[acomProvince_tbl] pr
        ON p.ProvinceId = pr.ProvinceId
           AND p.CountryId = pr.CountryId
WHERE bt.BookedDate
      BETWEEN '2019-04-01' AND '2019-05-22'
      --AND 'bookingsCY' = 0  
	  -- Th above commented out because it will always be false.  
	  --The string literal 'bookingsCY' is never equal to the string literal 0
GROUP BY 
	bt.title,
	p.countryId,
	p.provinceId,
	bt.MasterEstabId,
	pr.ActiveSearch
ORDER BY EstabName ASC,
         country ASC,
         bookingsPY DESC;

You need to group by the columns included in the select - here you are attempting to group by the value 'bt.EstabName' - not the column bt.EstabName.

The full group by should be:

GROUP BY bt.EstabName
       , p.countryId
       , p.provinceId
       , bt.MasterEstabId
       , pr.ActiveSearch