oracle – SQL not a single-group group function

oracle – SQL not a single-group group function

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

Not sure what SQL database server youre using but I suspect you want a query more like this (Written with a MSSQL background – may need some translating to the sql server youre 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))

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

oracle – SQL not a single-group group function

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *