I am completely stumped by this sql error. I am running MS SQL Server 2005. There is one instance running on our test server and another instance running on our staging server.
The query is as follows:
SET NOCOUNT ON
DECLARE @stationID as int
DECLARE @startDate as datetime
DECLARE @endDate as datetime
set @stationID = 3
set @startDate = '01-Feb-2008'
set @endDate = '12-Feb-2008'
DECLARE @tblTicketSummary TABLE
(ticketID INT, ticketPrice MONEY,
totalNumberSold INT, totalPrice MONEY)
INSERT INTO @tblTicketSummary
summaryDate >= @startDate
AND summaryDate <= @endDate
TMP.ticketID, MIN(T.ticketCode) AS ticketCode,
MIN(TG.groupName) AS groupName, MIN(TG.groupID) AS groupID,
MIN(T.ticketDescription) AS ticketDescription, SUM(TMP.totalNumberSold) AS sold,
TMP.ticketPrice, SUM(TMP.totalPrice) AS salesTotal
INNER JOIN tblTickets T
ON T.ticketID = TMP.ticketID
INNER JOIN tblTicketCodes TC
ON T.ticketCode = TC.ticketCode
INNER JOIN tblTicketGroups TG
ON TC.groupID = TG.groupID
SET NOCOUNT OFF
When I run this on the test server it works perfectly!!! No error and produces the desired results.
However, when I run this exact same query on the staging server with the exact same input parameters, it gives this error:
Msg 8127, Level 16, State 1, Line 25
Column "tblTicketGroups.groupID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
I can't for the life of me figure out why it would give this error!! Can someone please help me?