CODEWITHSUNDEEP
sqlselectgroup-byaggregate-functions
Lamloumi Afif
Lamloumi Afif

Reputation: 9081

Aggregation function && group by

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.

Upvotes: 0

Views: 60

Answers (2)

Blood-HaZaRd
Blood-HaZaRd

Reputation: 2124

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:

  • The GROUP BY can specify any number of valid expressions, including columns of the table.
  • Generally the 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.
  • The only items allowed in the select list of a SELECT that includes a GROUP BY clause are
    • Expressions that are specified in the GROUP BY
    • Aggregate functions
  • Expressions that are specified in the GROUP BY do not have to beincluded in the SELECT statement’s select list

2/ 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

Upvotes: 1

Azar
Azar

Reputation: 1867

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

Upvotes: 1

Related Questions