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

User · Accepted Answer

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

User · Answer

Maybe you find this simpler  select   from       select ssn  sum time  from downloads     group by ssn     order by sum time  desc   where rownum  lt   10 --top 10 downloaders   Regards K

User · Answer

If you want downloads number for each customer  use   select ssn        sum time    from downloads  group by ssn   If you want just one record -- for a customer with highest number of downloads -- use   select     from           select ssn                sum time            from downloads          group by ssn          order by sum time  desc           where rownum   1   However if you want to see all customers with the same number of downloads  which share the highest position  use   select     from           select ssn                sum time                 dense rank   over  order by sum time  desc  r           from downloads          group by ssn           where r   1

[sql] SQL not a single-group group function

Examples related to sql

Examples related to oracle

Examples related to group-by

Examples related to sum

Examples related to max