What is the wrong with the sql syntax

1
votes
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.

2
sqlsql-serverselectsql-server-2012syntax-error
If you have a SELECT which has a GROUP BY clause, every column in the SELECT list 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 – marc_s
Exactly what the error message sais: Countries.Name is not contained in the GROUP BY clause. – TT.
Not knowing your data, this may be as simple as just replacing a.Name with MAX(a.Name) in the SELECT clause. – Damien_The_Unbeliever
But it is possible in mysql. So how can I achieve this query result using sql server 2012.@marc_s & @TT – Al Amin Chayan
@Chayan - it's a documented extension in MySQL and a not terribly good one (IMO). – Damien_The_Unbeliever

2 Answers

6
votes

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
3
votes

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