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
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
Hi, and thankyou for trying to help me out! I've tried this and it does
not work. Here are the problems :
1) If a user has never logged in he doesn't show the user in the list
2) It doesn't count if it is 0 it's not on the liste and if the user
has logged in more than once the result is 1
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
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 [EMAIL PROTECTED]
02/19/2008 05:29 PM
To
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,
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
Richard,
1) If a user has never logged in he doesn't show the user in the list
2) It doesn't count if it is 0 it's not on the liste and if the user
has logged
in more than once the result is 1 (because of the group by ...).
Do you mean by #1 that you want to list all users whether they have