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

Question

I've been getting this error when doing what I thought to be one of the simplest possible queries! I see other people have run into issues here too, and I've looked through every solution I've seen but they're with more involved queries so it was very hard for me to pick out the problem. I made a little 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 find out how many times each name shows up, to produce a table like:

3 Mel
2 Mandy
1 Lucy

Here's the query I think should work:

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

And I get the error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Help!

Answer 1

You have quotes around the Name field which are unneeded.

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 the ' on the GROUP BY:

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

If name is a text, then you need to cast it to VARCHAR, but you might be truncating your column.

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