CODEWITHSUNDEEP
sqlsql-serverselectsql-server-2012syntax-error
Al Amin Chayan
Al Amin Chayan

Reputation: 2500

What is the wrong with the sql syntax

SELECT 
    a.Name, a.About, 
    COUNT(b.Id) AS TotalCities, 
    SUM(b.NoOfDwellers) AS TotalCityDwellers 
FROM 
    Countries a 
LEFT JOIN 
    Cities b ON a.Id = b.CountryId 
WHERE 
    a.Name LIKE '%some str%'  
GROUP BY 
    a.Id 
ORDER BY 
    a.Name ASC

This SQL returns error:

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

Upvotes: 1

Views: 245

Answers (2)

Mureinik
Mureinik

Reputation: 312136

In a query contains a group by clause, every row in the result represents the entire group of rows that share the same value(s) of the column(s) they are grouped by. Therefore, every item you select must either be one of those columns, a row-based manipulation on them, or an aggregate function. It seems that you're trying to aggregate b's values according to a's name and about. If this is true, you should amend your group by clause accordingly:

SELECT 
    a.Name, a.About, 
    COUNT(b.Id) AS TotalCities, 
    SUM(b.NoOfDwellers) AS TotalCityDwellers 
FROM 
    Countries a 
LEFT JOIN 
    Cities b ON a.Id = b.CountryId 
WHERE 
    a.Name IS NULL 
GROUP BY 
    a.Name, a.About -- here!
ORDER BY 
    a.Name ASC

Upvotes: 3

marc_s
marc_s

Reputation: 755321

If you have a SELECT with a GROUP BY clause, every column in the SELECT list of columns must be either an aggregate (SUM, COUNT, MAX etc.), or then it has to be in the GROUP BY clause.

You have both a.Name and a.About in your SELECT list which are not handled by an aggregate - therefore, these two columns must appear in the GROUP BY clause 

SELECT 
    a.Name, a.About, 
    COUNT(b.Id) AS TotalCities, 
    SUM(b.NoOfDwellers) AS TotalCityDwellers 
FROM 
    Countries a 
LEFT JOIN 
    Cities b ON a.Id = b.CountryId 
WHERE 
    a.Name LIKE '%some str%'  
GROUP BY 
    a.Id, a.About, a.Name 
ORDER BY 
    a.Name ASC

Upvotes: 6

Related Questions