Help with SQL query (list strings and count in same query)

Question

Hi everybody,

I’m working on a small kind of log system to a webpage, and I’m having some difficulties with a query I want to do multiple things. I have tried to do some nested / subqueries but can’t seem to get it right.

I’ve two tables:

User = {userid: int, username}

Registered =  {userid: int, favoriteid: int}

What I need is a query to list all the userid’s and the usernames of each user. In addition, I also need to count the total number of favoriteid’s the user is registered with.

A user who is not registered for any favorite must also be listed, but with the favorite count shown as zero.

I hope that I have explained my request probably but otherwise please write back so I can elaborate.

By the way, the query I’ve tried with look like this:

SELECT user.userid, user.username FROM user,registered WHERE user.userid = registered.userid(SELECT COUNT(favoriteid) FROM registered)

However, it doesn’t do the trick, unfortunately

Kind regards Mestika

Answer 1

Select userid, username
    , ( Select Count(*) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

If favoriteId can be nullable then use

Select userid, username
    , ( Select Count(favoriteid) From Registered As R Where R.userid = U.userid ) As RegistrationCount
From User As U

Answer 2

Try something like this (untested)

Select u.userid, username, ISNULL(x.FavoriteCount, 0) as FavoriteCount
From User u 
Left Join
(Select userid, count(*) as FavoriteCount
From Registered
Group By userid) x
on u.userid = x.userid

By the way, this is SQL Server syntax. MySql syntax would be very similar, just swap out the ISNULL function with IFNULL. If this is another DBMS, then it should at least give you an idea.

Edit: According to OMG Ponies, COALESCE could be used instead of ISNULL to make it work across SQL Server/MySQL/Oracle/etc. I've verified this on SQL Server.

For more info on null functions, see here: http://www.w3schools.com/SQL/sql_isnull.asp

Answer 3

SELECT user.userid, user.username , count(favoriteid) FROM user,registered WHERE user.userid = registered.userid gorupby user.userid, user.username UNION SELECT user.userid, user.username , 0 FROM user WHERE user.userid NOT EXISITS (select 1 from registered where registered.userid = user.userid)