I have this Sql query
select U.DisplayName, U.Reputation , nombre = count(B.Id)
from Badges B, Users U
where U.Location like '%usa%'
and B.UserId = U.Id
group by B.Id
I don't understand why i got this error
Column 'Users.DisplayName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
1/ the Error means that the column 'Users.DisplayName' is not included in the Group list. Means that every column in the SELECTCLause must be an aggregate function or included in the Groûp by List.
The rules for forming a GROUP BY clause are as follows:
GROUP BY can specify any number of valid expressions, including columns of the table. GROUP BY is used to specify columns in the table that will contain common data, in order to “group” rows together for performing some sort of aggregate function on the set of rows.SELECT that includes a
GROUP BY clause are
GROUP BYGROUP BY do not have to beincluded in the SELECT statement’s select list2/ try To include The column in the select list :
select U.DisplayName, U.Reputation , nombre = count(B.Id)
from Badges B, Users U
where U.Location like '%usa%'
and B.UserId = U.Id
group by U.DisplayName, B.Id
Try this
select U.DisplayName, U.Reputation , nombre = count(B.Id)
from Badges B, Users U
where U.Location like '%usa%'
and B.UserId = U.Id
group by U.DisplayName,U.Reputation
group by B.Id