CODEWITHSUNDEEP
sqldatabaseoracle-databasegroup-byoracle10g
rakibtg
rakibtg

Reputation: 5911

max(count(*)) Error: single-group group function

Assume I have two tables:

  1. users
    enter image description here
  2. sells
    enter image description here

I need to alter this query: Show top three users who bought copies of software
My SQL is this:

select u.name, u.age, u.sex, u.email, s.selluid, max(count(u.uid)) FROM users u, sells s where u.usrid = s.selluid

Any idea about how to solve this problem? Thanks

Upvotes: 0

Views: 88

Answers (3)

potashin
potashin

Reputation: 44581

SELECT x.*
FROM (
       SELECT u.name
            , u.age
            , u.sex
            , u.email
            , s.selluid
            , COUNT(*) as t 
       FROM users u JOIN sells s ON u.usrid = s.selluid
       GROUP BY u.name
       ORDER BY COUNT(*) DESC
     ) x 
 WHERE ROWNUM <= 3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can solve this using an aggregation subquery with row_number():

select u.*, s.numsales
from users u join
     (select s.selluid, count(*) as numsales,
             row_number() over (order by count(*) desc) as seqnum
      from sells s
      group by s.selluid
     ) s
     on u.userid = s.selluid
where seqnum <= 3;

One advantage to this approach is that you can readily get all the columns from users using just u.*.

Upvotes: 1

Puffy
Puffy

Reputation: 30

Try this

select u.usrid, u.name, count(s.sellid) 
from users u left join sells s on u.usrid=s.selluid 
group by u.usrid, u.name order by count(s.sellid) desc;

Upvotes: 1

Related Questions