SQL not a single-group group function

Question

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

Answer 1

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