SQL error when using Group By: Each GROUP BY expression must contain at least one column that is not an external reference

Question

I get this error when I do what I thought was one of the simplest queries! I see that other people are also having problems, and I looked through all the solutions that I saw, but they have more requests, so it was very difficult for me to choose the problem. I made a small dummy table to illustrate my problem.

table name: grouptest

id name 1 Mel 2 Lucy 3 Mandy 4 Mel 5 Mandy 6 Mel 

I want to know how many times each name appears to create a table like:

 3 Mel 2 Mandy 1 Lucy 

Here's a query that I think should work:

 SELECT Count(id), Name FROM groupbytest GROUP BY 'Name' 

And I get the error message:

Each GROUP BY expression must contain at least one column, which is not an external reference.

Help!

Answer 1

You have quotes around the Name field that are not needed.

 SELECT Count(id), Name FROM grouptest GROUP BY Name 

Based on your comments, you need to CAST your Name column:

 SELECT Count(id), Cast(Name as Varchar(max)) Name FROM grouptest GROUP BY Cast(Name as Varchar(max)) 

Answer 2

Lose ' on GROUP BY :

 SELECT Count(id), Name FROM groupbytest GROUP BY Name 

If name is text , you need to point it to VARCHAR , but you can truncate your column.

 SELECT Count(id), CAST(Name AS VARCHAR(8000)) AS Name FROM groupbytest GROUP BY CAST(Name AS VARCHAR(8000)) 

Answer 3

Do you want to:

 SELECT Count(id), [Name] FROM groupbytest GROUP BY [Name] 

GROUP BY 'Name' trying to group by the literal row "Name", not the Name column.

Name can also be a reserved word, so the brackets around it, although you better not name the Name column.