CODEWITHSUNDEEP
sql-server
Delyana Boyadzhieva
Delyana Boyadzhieva

Reputation: 121

SQL Error message: Column XXX is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SELECT 
    t.[Week], MAX(t.Revenue) AS 'Max Revenue' 
FROM 
    (SELECT 
         [Week], SUM([Service Total]) AS Revenue 
     FROM 
         dbo.['Data - 2017 Appmt Time$'] 
     GROUP BY 
         [Week]) t;

Error:

Column 't.Week' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I'm trying to do is to get the max revenue and the respective week for which is that max revenue. How to make that code working? The code is working if I don't select t.[Week] in the beginning but then I get only the max revenue. If I include t.[Week] in a group by clause, as the message suggests, that I have a list of all revenues week-by-week, but I don't need it. I only need the max revenue and for which week it is. Apologies if my question is stupid, I'm a beginner in SQL and already lost much time searching for a solution. :( Thanks a lot in advance!

Upvotes: 0

Views: 1973

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You cannot use a non aggregate column in select which is not part of Group By.

Actually you don't need derived table for this, to find the week with maximum revenue use TOP 1 with Order by

SELECT top 1 [Week],
             Revenue = SUM([Service Total])  
FROM dbo.['Data - 2017 Appmt Time$'] 
GROUP BY [Week]
order by Revenue desc

As mentioned by Dan Guzman, if there is a tie in maximum revenue and you want all the tie records then replace TOP 1 with TOP 1 with Ties

Upvotes: 1

Related Questions