SQL不是单组功能 [英] SQL not a single-group group function

问题描述

  SELECT MAX（SUM（TIME））
 FROM downloads $当我运行以下SQL语句时： b $ b GROUP BY SSN 
  

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

  SELECT SSN，MAX（ SUM（TIME））
 FROM downloads 
 GROUP BY SSN 
  

以下错误：

不是单一群组函数

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

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

我认为这是说
- 删除组函数使得和值无效
- 丢弃单个列表达式（SSN ）只会给我最高额
- 不知道第三部分是什么。

任何人都可以向正确的方向引导吗？

-Tomek

编辑：这个数据库中的TIME指的是下载次数

好的问题是，查询中特定SSN的SUM（TIME）是单个值，所以它反对MAX，因为它毫无意义（The最大的单个值是没有意义的）。

不确定您使用的是哪种SQL数据库服务器，但我怀疑您希望查询更多像这样（用MSSQL背景编写 - 可能需要一些翻译到你正在使用的sql服务器）：

  SELECT TOP 1 SSN，SUM（TIME）
 FROM downloads 
 GROUP BY SSN 
 ORDER BY 2 DESC 
  

这将为您提供总时间和总时间最多的SSN。

编辑 - 如果您有多个时间相等希望他们所有你会用的：

  SELECT 
 SSN，SUM（TIME）
 FROM downloads 
 GROUP BY SSN 
 HAVING SUM（TIME）=（SELECT MAX（SUM（TIME））FROM downloads GROUP BY SSN））
  

When I run the following SQL statement:

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

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 get the following error:

not a single-group group function

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

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

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.

Could anyone guide in the right direction?

-Tomek

EDIT: TIME in this database refers to the number of times downloaded

解决方案

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).

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

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屋！