I have two table login_info_1,login_info_2 which has a column user_id in each table select user_id,count(user_id) as cnt from login_info_1 group by user_id; which gives me result like this user_id cnt 101 4 102 5 103 1 107 7 same for the second table select user_id,count(user_id) as cnt2 from login_info_2 group by user_id; user_id cnt 101 3 102 2 103 2 107 1 I want to combine these two tables and the results should look like this user_id cnt 101 7 102 7 103 3 107 2 Database: MYSQL
select a.user_id, count(b.user_id) from login_info_1 a join login_info_2 b on a.user_id=b.user_id group by a.user_id On Fri, Jul 29, 2011 at 12:44 PM, The sherminator <steve.kim41@gmail.com>wrote:
I have two table login_info_1,login_info_2 which has a column user_id in each table
select user_id,count(user_id) as cnt from login_info_1 group by user_id;
which gives me result like this
user_id cnt 101 4 102 5 103 1 107 7
same for the second table select user_id,count(user_id) as cnt2 from login_info_2 group by user_id;
user_id cnt 101 3 102 2 103 2 107 1
I want to combine these two tables and the results should look like this
user_id cnt 101 7 102 7 103 3 107 2
Database: MYSQL
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
this solution worked ok SELECT user_id, COUNT(user_id) FROM ( SELECT user_id FROM login_info_1 UNION ALL SELECT user_id FROM login_info_2 ) AS lgin_info GROUP BY user_id; me think the theory pretty much works the same way thanks On Fri, Jul 29, 2011 at 1:22 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
select a.user_id, count(b.user_id) from login_info_1 a join login_info_2 b on a.user_id=b.user_id group by a.user_id
On Fri, Jul 29, 2011 at 12:44 PM, The sherminator <steve.kim41@gmail.com>wrote:
I have two table login_info_1,login_info_2 which has a column user_id in each table
select user_id,count(user_id) as cnt from login_info_1 group by user_id;
which gives me result like this
user_id cnt 101 4 102 5 103 1 107 7
same for the second table select user_id,count(user_id) as cnt2 from login_info_2 group by user_id;
user_id cnt 101 3 102 2 103 2 107 1
I want to combine these two tables and the results should look like this
user_id cnt 101 7 102 7 103 3 107 2
Database: MYSQL
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Do an EXPLAIN on both and see how they perform On Fri, Jul 29, 2011 at 2:19 PM, The sherminator <steve.kim41@gmail.com>wrote:
this solution worked ok
SELECT user_id, COUNT(user_id) FROM ( SELECT user_id FROM login_info_1 UNION ALL SELECT user_id FROM login_info_2 ) AS lgin_info GROUP BY user_id;
me think the theory pretty much works the same way thanks
On Fri, Jul 29, 2011 at 1:22 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
select a.user_id, count(b.user_id) from login_info_1 a join login_info_2b on a. user_id=b.user_id group by a.user_id
On Fri, Jul 29, 2011 at 12:44 PM, The sherminator <steve.kim41@gmail.com>wrote:
I have two table login_info_1,login_info_2 which has a column user_id in each table
select user_id,count(user_id) as cnt from login_info_1 group by user_id;
which gives me result like this
user_id cnt 101 4 102 5 103 1 107 7
same for the second table select user_id,count(user_id) as cnt2 from login_info_2 group by user_id;
user_id cnt 101 3 102 2 103 2 107 1
I want to combine these two tables and the results should look like this
user_id cnt 101 7 102 7 103 3 107 2
Database: MYSQL
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
Hey peter No a join will not do the same job Let's say you have sample data of. info1 A,1 A,2 B,1 Info2 A,3 B,6 the desired output is A 3 B 2 (which you get with a union) a join will give you A 2 B 1 On Fri, Jul 29, 2011 at 3:01 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Do an EXPLAIN on both and see how they perform
On Fri, Jul 29, 2011 at 2:19 PM, The sherminator <steve.kim41@gmail.com>wrote:
this solution worked ok
SELECT user_id, COUNT(user_id) FROM ( SELECT user_id FROM login_info_1 UNION ALL SELECT user_id FROM login_info_2 ) AS lgin_info GROUP BY user_id;
me think the theory pretty much works the same way thanks
On Fri, Jul 29, 2011 at 1:22 PM, Peter Karunyu <pkarunyu@gmail.com>wrote:
select a.user_id, count(b.user_id) from login_info_1 a join login_info_2b on a. user_id=b.user_id group by a.user_id
On Fri, Jul 29, 2011 at 12:44 PM, The sherminator <steve.kim41@gmail.com
wrote:
I have two table login_info_1,login_info_2 which has a column user_id in each table
select user_id,count(user_id) as cnt from login_info_1 group by user_id;
which gives me result like this
user_id cnt 101 4 102 5 103 1 107 7
same for the second table select user_id,count(user_id) as cnt2 from login_info_2 group by user_id;
user_id cnt 101 3 102 2 103 2 107 1
I want to combine these two tables and the results should look like this
user_id cnt 101 7 102 7 103 3 107 2
Database: MYSQL
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu ------------------- Web Performance Evangelist *Make your website and web app faster.*
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (2)
-
Peter Karunyu -
The sherminator