SQL 不是单组组函数 [英] SQL not a single-group group function

问题描述

当我运行以下 SQL 语句时:

When I run the following SQL statement:

SELECT MAX(SUM(TIME))
FROM downloads
GROUP BY SSN

它返回客户下载的最大总和值，但是，如果我尝试通过将其添加到 select 语句中来查找该最大值所属的社会保险号:

It returns the maximum sum value of downloads by a customer, however if I try to find the social security number that that max value belongs to by adding it to the select statement:

SELECT SSN, MAX(SUM(TIME))
FROM downloads
GROUP BY SSN

我收到以下错误:

不是单组群功能

我不明白为什么它会抛出这个错误.谷歌搜索提出了以下操作:

I do not understand why it is throwing this error. A google search came up with the following action:

从 SELECT 列表中删除组函数或单个列表达式，或者添加一个包含列出的所有单个列表达式的 GROUP BY 子句

Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed

从我认为这是说- 删除分组功能会使总和值无效- 删除单个列表达式 (SSN) 只会给我最大总和- 不确定第三部分.

From what I think this is saying - dropping the group function makes the sum value invalid - droping the individual column expression (SSN) will just give me the max sum - not sure about that third part.

谁能指导正确的方向?

-托梅克

TIME 在这个数据库中是指下载的次数

TIME in this database refers to the number of times downloaded

推荐答案

简单地说，问题是查询中特定 SSN 的 SUM(TIME) 是单个值，因此它反对 MAX，因为它使没有意义(单个值的最大值是没有意义的).

Well the problem simply-put is that the SUM(TIME) for a specific SSN on your query is a single value, so it's objecting to MAX as it makes no sense (The maximum of a single value is meaningless).

不确定您使用的是什么 SQL 数据库服务器，但我怀疑您想要更像这样的查询(使用 MSSQL 背景编写 - 可能需要一些翻译到您正在使用的 sql 服务器):

Not sure what SQL database server you're using but I suspect you want a query more like this (Written with a MSSQL background - may need some translating to the sql server you're using):

SELECT TOP 1 SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
ORDER BY 2 DESC

这会给你最高总时间和总时间的SSN.

This will give you the SSN with the highest total time and the total time for it.

编辑 - 如果你有多个相同的时间并且想要它们全部你会使用:

Edit - If you have multiple with an equal time and want them all you would use:

SELECT
SSN, SUM(TIME)
FROM downloads
GROUP BY SSN
HAVING SUM(TIME)=(SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN))

这篇关于SQL 不是单组组函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！