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

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

Solution 2

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

Solution 3

Maybe you find this simpler

select * from (
    select ssn, sum(time) from downloads
    group by ssn
    order by sum(time) desc
) where rownum <= 10 --top 10 downloaders

Regards
K

Comments

[Tomek]

I am using oracle and am unfamiliar with grabbing specific rows. However what if there are customers who are tied for having the maximum amount of downloads?

[fyjham]

If there was multiple with the same amount it'd essentially be random which you got (There's probably an order to it at a low level but I wouldn't know nor rely on it). You could add additional clauses to the order by if you'd like to specify how to order it yourself. If you want to get all the ones tied for first I believe you'll need nested queries, one to get the max time then another to pull back all the ones that meet it (Something like "SELECT SSN, SUM(TIME) FROM downloads GROUP BY SSN HAVING SUM(TIME)=(SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN))")

[fyjham]

PS: Not 100% sure whether HAVING exists in Oracle - someone else may be able to clarify that. Most my experience is TSQL.

[Tomek]

nice, having worked. I tried it before but i tried having sum(time) = max(sum(time)). I cant really accept a comment as an answer so i guess ill just accept your answer.

[Pantelis Peslis]

Your second example would give him the customer with the lowest number unless your order by descending