List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 19 2008 5:09pm
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  
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
>
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