List:General Discussion« Previous MessageNext Message »
From:David Schneider-Joseph Date:February 19 2008 10:59pm
Subject:Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
View as plain text  
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=david@stripped
>
>

Thread
group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?Richard19 Feb
  • Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?Peter Brawley19 Feb
    • Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?Richard19 Feb
      • Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?Peter Brawley20 Feb
    • Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?Richard19 Feb
      • Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?ddevaudreuil19 Feb
      • Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?David Schneider-Joseph19 Feb
        • Re: group a select * and a select COUNT from 2 different [... ] (solvedthankyou !)Richard20 Feb
Re: group a select * and a select COUNT from 2 different tables usingresult of first table to do the COUNT ... is it possible ?ddevaudreuil19 Feb