List:General Discussion« Previous MessageNext Message »
From:ddevaudreuil Date:February 19 2008 10:54pm
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  
Actually, this works too:

 SELECT a.username, a.first_name, a.last_name, Count(b.username) as count
 FROM user_list a
 LEFT OUTER JOIN login_table b ON a.username = b.username
 GROUP BY a.username,a.first_name,a.lastname;


__________________

Try
 SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is 
null then 0 else 1 end) as count
 FROM user_list a
 LEFT OUTER JOIN login_table b ON a.username = b.username
 GROUP BY a.username,a.first_name,a.lastname;

Donna



Richard <mysql_list@stripped> 
02/19/2008 05:29 PM

To
peter.brawley@stripped, mysql@stripped
cc

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 ?






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


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



CONFIDENTIALITY NOTICE:This email is intended solely for the person or 
entity to which it is addressed and may contain confidential and/or 
protected health information.  Any duplication, dissemination, action 
taken in reliance upon, or other use of this information by persons or 
entities other than the intended recipient is prohibited and may violate 
applicable laws.  If this email has been received in error, please notify 
the sender and delete the information from your system.  The views 
expressed in this email are those of the sender and may not necessarily 
represent the views of IntelliCare.


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