Sql选择group by error：字段不包含在聚合函数或group by子句中 [英] Sql select group by error: field not contained in aggregate function or group by clause

问题描述

SELECT a.userName, productType, MAX(TotalSales) 
	FROM(
		SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
			FROM [SaleProduct] SP
				JOIN [Sale] S ON SP.SaleId = S.SaleId
					JOIN [User] U ON U.UserId = S.UserId
						GROUP BY SP.Type, U.UserName) AS a
		GROUP BY productType, a.userName
		)

给了我

gives me

Name| Product-Type | Sales
XYZ | Laptops| 999
ABC | Desktop| 888
ABC | Servers|777
XYZ | Servers|555

我想：

I want:

Name| Product Type|Sales
XYZ |Laptops| 999
ABC |Desktop| 888
ABC |Servers|777

如果我只需要返回上面的数组（ABC总销售额最多服务器），我应该如何编写查询？

下面的查询工作，期望我仍然需要从外部查询返回名称（但是通过加入Name并将其包含在外部查询中，它还返回XYZ，总销售额为555.怎么做？



我尝试过：

If I only need to return the array above ( ABC having the most total sales of servers ), how should I write the query ?
The Query below works , expect that I still need to return "Name" from the outer query (but by joining Name and including it in the outer query , it also return XYZ with total sales of 555. How to do it ?

What I have tried:

SELECT productType, MAX(TotalSales) 
	FROM(
		SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
			FROM [SaleProduct] SP
				JOIN [Sale] S ON SP.SaleId = S.SaleId
					JOIN [User] U ON U.UserId = S.UserId
						GROUP BY SP.Type, U.Name) AS a
		GROUP BY productType
		)

上面的查询工作预计我还需要从外部SELECT查询返回Name

怎么做？

the above query works expect I still need to return Name from the outer SELECT query
how to do this ?

推荐答案

请参阅此处： SQL GROUP由于列'名称'在选择列表中无效，因为......错误 [ ^ ]

select语句中您未执行聚合函数（Sum，Avg，Max等）的每个字段都必须列在Group By子句。另外，Group By不允许使用字段别名，你应该在那里使用实际的字段名。



如果你让你的例子使用子查询，那么你需要做的是在外部查询中引用用户的my别名，然后记住对其进行分组。

Every field in your select statement that you are not performing an aggregate function on (Sum, Avg, Max, etc) must be listed in the Group By clause. Also, the Group By does not allow field aliases, you should use the actual field name there.

If you get your example working with the sub-query, all you need to do is reference the 'my' alias for your user in the outside query, then remember to group on it.

其他海报正在查看您在组上的错误，我将尝试回答基本问题。



我认为你想要做的只是返回排名最高的用户名基于产品类型的总销售额。最简单的方法是使用带有PARTITION BY的ROW_NUMBER函数 - 参见 ROW_NUMBER（Transact-SQL）| Microsoft Docs [ ^ ]



例如：

The other posters are picking up on your errors on the group by, I'm going to attempt to answer the underlying question.

I think what you are trying to do is just return the highest ranking UserName based on total sales by Product Type. The easiest way to do that is to use the ROW_NUMBER function with PARTITION BY - see ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]

For example:

;with CTE AS
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
)
SELECT CTE.* FROM CTE WHERE rn = 1

我选择了使用公用表表达式，因为我发现它们更容易遵循 - 使用子查询的等价物将是

I've chosen to use a Common Table Expression as I find them easier to follow - the equivalent using a sub-query would be

SELECT * FROM 
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
) as a
WHERE rn = 1

注意事项：

- 我用过PARTITION BY重新启动每个组内的编号（在本例中为产品类型）。如果我只是运行内部查询，我得到这些结果：

Points to note:
- I've used PARTITION BY to restart the numbering within each "group" of things (in this case product types). If I just run the inner query I get these results:

ABC	Desktop	888.00	1
XYZ	Laptop	999.00	1
ABC	Server	777.00	1
XYZ	Server	555.00	2

请注意 rn 显示谁是1号，2号等人。



该解决方案没有考虑关系（即两个销售人员对特定产品的销售额完全相同）。联合赢家！为了满足这种情况，您最好使用RANK - 请参阅 RANK（Transact-SQL）| Microsoft Docs [ ^ ]

Notice that rn is showing who came in at number 1, number 2 etc.

This solution does not take into account ties (i.e. two salemen have exactly the same amount of sales for a specific product). Joint winners! To cater for that scenario you would be better off using RANK - see RANK (Transact-SQL) | Microsoft Docs[^]

这篇关于Sql选择group by error：字段不包含在聚合函数或group by子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！