Re: group a select * and a select COUNT from 2 different [... ] (solved thankyou !)

[Richard]

Hello, thankyou to everyone who has helped me out on this one as I did 
not think it was actuallay possible ! :)

This is what worked best for me :

SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 
0) AS count
FROM user_list a
LEFT JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

I'm sorry if I was not clear with my first email yesterday making it 
sound like I wanted a 0 or a 1 and nothing else... I made a mistake and 
thought that I was getting nothing or 1 whereas it was actually counting 
corectly.

I was also suggested a LEFT OUTER JOIN but have read that it is a 
synonym to LEFT JOIN, is this the case or is there a difference between 
the two?

Thanks again,

Richard

David Schneider-Joseph a écrit :
> Try this one:
> 
> SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 
> 0) AS count
> FROM user_list a
> LEFT JOIN login_table b ON a.username = b.username
> GROUP BY a.username,a.first_name,a.lastname;
> 
> The LEFT JOIN will ensure you still get a result row even if there are 
> no matching rows in `login_table`.  And the COALESCE will give you a 
> value of 0 instead of NULL for the count, in that case.
> 
> On Feb 19, 2008, at 5:29 PM, Richard wrote:
> 
>> Sorry it's me again, I made a mistake, it counts the number of logins 
>> correctly, but does not show members with 0 logins !
>>
>> Any idea how to do this?
>>
>> Thanks :)
>>
>> Peter Brawley a écrit :
>>> Richard,
>>> >Can I do something like this :
>>> >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
>>> count
>>> >FROM login_table b WHERE a.username = b.username) FROM user_list a
>>> Try ...
>>> SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
>>> FROM user_list a
>>> JOIN login_table b ON a.username = b.username
>>> GROUP BY a.username,a.first_name,a.lastname;
>>> PB
>>> -----
>>> Richard wrote:
>>>> Hello,
>>>>
>>>> This time I'm rearly not sure if this is possible to do. I've got 
>>>> two queries that I would like to bring together to make only one 
>>>> query ...
>>>>
>>>> I've got a list of users
>>>>
>>>> And also a login table
>>>>
>>>> I would like to list all users and show the number of times they 
>>>> have logged in.
>>>>
>>>> So to get the list of users I would do :
>>>>
>>>> SELECT username, first_name, last_name FROM user_list
>>>>
>>>> And to count the number of connections I would do
>>>>
>>>> SELECT COUNT(*) AS count FROM login_table WHERE username = 
>>>> $result['username']
>>>>
>>>> Can I do something like this :
>>>>
>>>> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
>>>> count FROM login_table b WHERE a.username = b.username) FROM 
>>>> user_list a
>>>>
>>>> I know that the above query can not work but It's just to give a 
>>>> better idea about what I'm trying to do . :)
>>>>
>>>> If I do a join, I will the username repeated for each login.
>>>>
>>>> Thanks in advance,
>>>>
>>>> Richard
>>>>
>>
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
> 
>