CODEWITHSUNDEEP
sql-serversql-server-2008
Amrit
Amrit

Reputation: 421

ORDER BY clause error

Hey guys I am getting this exception when trying to run the query

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

Query:

SELECT 
    T1.restaurantId AS ID, 
    COUNT(T2.post_id) AS favCount 
FROM   
    restaurantData AS T1 
INNER JOIN 
    favoriteData AS T2 ON T1.restaurantId = T2.post_id 
ORDER BY 
    favCount DESC, ID DESC

It says that I have to add all select columns which are not aggregated in ORDER BY clause, which I already did for restaurantID. But its still giving me error.

Upvotes: 2

Views: 5330

Answers (2)

marc_s
marc_s

Reputation: 754258

You're using an aggregate function (COUNT), so you have to use a GROUP BY clause as well:

SELECT 
    T1.restaurantId AS ID, 
    COUNT(T2.post_id) AS favCount 
FROM   
    restaurantData AS T1 
INNER JOIN 
    favoriteData AS T2 ON T1.restaurantId = T2.post_id 
GROUP BY
    T1.restaurantID
ORDER BY 
    favCount DESC, ID DESC

Basically, with your query, you want to count the rows found - and you want the result grouped by each restaurantId - so that's what the GROUP BY clause expresses.

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 452977

You are missing GROUP BY T1.restaurantId in the query.

SELECT T1.restaurantId   AS ID, 
       COUNT(T2.post_id) AS favCount 
FROM   restaurantData AS T1 
       INNER JOIN favoriteData AS T2 
         ON T1.restaurantId = T2.post_id 
GROUP BY T1.restaurantId
ORDER  BY favCount DESC, 
          ID DESC

If you have no GROUP BY clause at all then this gives you an aggregate of the whole source table (in which case inclusion of restaurantId in the select list is invalid without wrapping that in an aggregate) rather than broken into groups by restaurant as you clearly desire.

Upvotes: 4

Related Questions