列'dbo.User.FB_UserId'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中 [英] 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

问题描述

我在这里看到这个错误消息很多次，但我没有从中解决我的特定问题（可能是因为我不是一个sql专家），所以请原谅我发布一个问题到$相同的错误。

这是我试图执行的查询：

  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.CriceFbCredits = 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 
  

如果我确实，我收到错误消息。好吧，我知道它是什么意思，我理解该怎么做，但我认为如果我这样做，不会给我我想要的结果...
我想要一些特定的用户ID 。一些数据需要进行汇总，其中一些需要计数，并且在结果列表中每个id应该只出现一次。

现在有趣的是我）。如果我写这样的内部SELECT查询，我没有得到错误。但是我不知道返回的数据是否正确：

 ，（SELECT COUNT（PriceFbCredits）
 FROM [ dbo]。[FbCreditsCallback] 
 WHERE UserID = U.Id）AS Payments 
  

.. 。老实说，我有点失落，我希望有一些帮助。

解决方案

下面你会看到在使用分组来制作聚合函数时正确 SQL Server 语法。

WHERE UserID = U.Id 根本不需要，因为您将它作为 INNER JOIN 子句的一部分。

  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 WH EN 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]。[用户] 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 
  

如您所写

我不是一个sql专家

从现在起，避免使用WITH （NOLOCK），就像你问 SELECT [data] FROM [TABLE] WITH（我真的，真的不关心它是否准确）

在某些情况下有这样做的理由，但如果您是 SQL 的初学者，我怀疑您是在这种情况下。

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.

解决方案

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.

这篇关于列'dbo.User.FB_UserId'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！