Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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 logged in or not? #2 is less clear still; does it mean the query is to show a count of 0 for no logins and 1 for any positive number of logins? If so, try... SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) 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; PB Richard wrote: 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 (because of the group by ...). Thankyou 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/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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 <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- 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.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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 [EMAIL PROTECTED], mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- 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.
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/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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 (because of the group by ...). Thankyou 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/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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/[EMAIL PROTECTED]
