Not a single-group group function

Question

I have some tables which basically look as follows:

TBL_USER
user_id - number
user_name - varchar

TBL_STUFF
stuff_id - number
stuff_user_id - number

I want to query for all user information including the number of "stuff" they have. I was trying something like this:

select user_id, user_name, count(stuff_id) 
  from tbl_user
  left outer join tbl_stuff on stuff_user_id = user_id
 where user_id = 5;

but I get an error which says "not a single-group group function"

Is there some other way I should be doing this?

Answer 1

Well, you are missing the group function ;-)

Try this:

select user_id, user_name, count(stuff_id) 
from tbl_user left outer join tbl_stuff on stuff_user_id = user_id
where user_id = 5
group by user_id, user_name;

The last line is the group by clause that tells Oracle to count all rows with the same user_id and user_name combination.

Answer 2

You could also do it like this:

select 
  user_id, 
  user_name, 
  (
    SELECT
        COUNT(*)
    FROM
        tbl_stuff
    WHERE 
        stuff_user_id = tbl_user.user_id

  ) AS StuffCount, 
from 
   tbl_user
where 
   user_id = 5;