Column 'dbo.User.FB_UserId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I saw this error-message lots of times here, but I'm not getting the solution for my specific problem out of it (probably because I'm not an sql-expert), so please forgive me for posting a question to the same error.

This is the query I'm trying to execute:

SELECT DISTINCT U.FB_UserId
 , U.Id AS GameUserID
 , U.FbLocale
 , U.FbGender
 , U.FbBirthday
 , U.RegistredAt
 , U.LoginCount
 , U.PlayCount
 , U.MarketGroupId

    , (SELECT COUNT(C.PriceFbCredits)
    WHERE UserID = U.Id) AS Payments

    , (SELECT SUM(CASE WHEN C.PriceFbCredits = 13 THEN 1 END)
    WHERE UserID = U.Id) AS P13

    , (SELECT SUM(CASE WHEN C.PriceFbCredits = 52 THEN 1 END)
    WHERE UserID = U.Id) AS P52

    , (SELECT SUM(CASE WHEN C.PriceFbCredits = 130 THEN 1 END)
    WHERE UserID = U.Id) AS P130

FROM [dbo].[User] AS U WITH (NOLOCK) INNER JOIN [dbo].[FbCreditsCallback] AS C WITH (NOLOCK) ON C.UserId = U.Id

If I do, I get the error-message. OK, I know what it means and I kind of understand what to do, but I think if I do it doesn't give me the result I want... I want some the data for a specific userid. some of the data needs to be summed, some of them need to be counted and in the result list each id should only appear once.

Now here's the funny thing (for me). If I write the inner SELECT-Queries like this, I'm not getting the error. But I don't know if the returned data is correct:

, (SELECT COUNT(PriceFbCredits)
FROM [dbo].[FbCreditsCallback]
WHERE UserID = U.Id) AS Payments

... To be honest, I kind of lost track and I'm hoping for some help.

Select without a From and with a Where, I dont even know how it works. The second query you mentioned will always work as it is the right syntax for a query. Coming to the problem, why dont you use the aggregate functions without the Select? Example Count(c.PriceFbCredits) which should work as you have an inner join on the UserId. — Nilesh

Luis LL Luis LL · Accepted Answer · 2013-08-19T09:58:58

below you'll see the correct SQL Server syntax when using grouping to make aggregation functions.

your WHERE UserID = U.Id is not needed at all since you are making it as part of the INNER JOIN clause.

So try this:

SELECT DISTINCT U.FB_UserId
 , U.Id AS GameUserID
 , U.FbLocale
 , U.FbGender
 , U.FbBirthday
 , U.RegistredAt
 , U.LoginCount
 , U.PlayCount
 , U.MarketGroupId
 , COUNT(*)  AS Payments
 , SUM(CASE WHEN C.PriceFbCredits = 13 THEN 1 ELSE 0 END) AS P13
 , SUM(CASE WHEN C.PriceFbCredits = 52 THEN 1 ELSE 0 END) AS P52
 , SUM(CASE WHEN C.PriceFbCredits = 130 THEN 1 ELSE 0 END) AS P130

FROM [dbo].[User] AS U WITH (NOLOCK) 
INNER JOIN [dbo].[FbCreditsCallback] AS C WITH (NOLOCK) ON C.UserId = U.Id
GROUP BY U.FB_UserId
 , U.Id 
 , U.FbLocale
 , U.FbGender
 , U.FbBirthday
 , U.RegistredAt
 , U.LoginCount
 , U.PlayCount
 , U.MarketGroupId

as you wrote

i'm not an sql-expert

From now on, avoid using WITH(NOLOCK), is the same as if you asked SELECT [data] FROM [TABLE] WITH(I really, really don't care if it is accurate or not)

there are reasons to do that in some cases, but if you are a beginner with SQL I doubt that you are in such situation.

Column 'dbo.User.FB_UserId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

2 Answers