MYSQL select number of elements FOREACH element in another table

Question

I have two databases, each containing one table, users.user_groups and logins.logins.

The first one contains a list of users and their corresponding groups as follows :-

mysql>select * from users.user_groups;

+----------+-------------------+
| username | group_type |
+----------+-------------------+
| user_1 | group_1 |
| user_2 | group_1 |
| user_3 | group_1 |
| user_4 | group_2 |
| user_5 | group_2 |
| user_6 | group_2 |
| user_7 | group_3 |
| user_8 | group_3 |
| user_9 | group_3 |
+----------+---------+

so user_1, user_2, user_3 form part of group_1, user_4, user_5, user_6 form part of group_2 etc.

Table logins, inside database logins contains a track record of when the users log in the system :-

select * from logins.logins;

+-----------+---------------------+
| user_name | login_date |
+-----------+---------------------+
| user_1 | 2014-06-01 09:11:09 |
| user_1 | 2014-06-02 02:11:09 |
| user_2 | 2014-06-08 02:13:43 |
| user_1 | 2014-06-02 03:13:42 |
| user_3 | 2014-06-02 03:13:42 |
| user_2 | 2014-06-02 03:13:42 |
| user_1 | 2014-06-08 03:13:42 |
| user_4 | 2014-06-02 03:13:42 |
| user_5 | 2014-06-02 03:13:42 |
| user_8 | 2014-06-02 03:13:42 |
| user_9 | 2014-06-02 03:13:42 |
+-----------+---------------------+
11 rows in set (0.00 sec)

I need to gather statistics of how the groups are logging in the system, and I managed this so far :-

SELECT user_name,  group_type, COUNT(*) as number_of_logins
FROM logins.logins, users.user_groups 
WHERE username = user_name and group_type = 'group_2' 
GROUP BY user_name 
ORDER BY group_type, user_name;

which gives me

+-----------+------------+------------------+
| user_name | group_type | number_of_logins |
+-----------+------------+------------------+
| user_4 | group_2 | 1 |
| user_5 | group_2 | 1 |
+-----------+------------+------------------+
2 rows in set (0.00 sec)

But I also need it to tell me user_6 has 0 number of logins like this :-

+-----------+------------+------------------+
| user_name | group_type | number_of_logins |
+-----------+------------+------------------+
| user_4 | group_2 | 1 |
| user_5 | group_2 | 1 |
| user_6 | group_2 | 0 |
+-----------+------------+------------------+

I would be grateful if anyone would point me in the right direction

I have managed a solution via php using for / next loops, but I believe it would be faster via straight sql

Thanks in advance, and apologies for the lengthy question

J

I am sorry I was re-editing the whole thing, coz it looked like a mess, so I was adding "br" html tags to the tables. Must have deleted the edits by mistake than. Sorry My first post here — user3765349
– user3765349, Commented Jun 22, 2014 at 20:04
Possible duplicate? "How to include NULL values in a query with Outer Join and Group By" stackoverflow.com/questions/8662833/… — bloodyKnuckles
– bloodyKnuckles, Commented Jun 22, 2014 at 20:09

Rahul · Accepted Answer · 2014-06-22 20:13:27Z

1

Try using LEFT JOIN instead of INNER JOIN like (ug and ul are table alias)

SELECT ug.user_name,  
ug.group_type, 
COUNT(ul.user_name) as number_of_logins
FROM users.user_groups ug
LEFT JOIN logins.logins ul ON ug.username = ul.user_name 
and ug.group_type = 'group_2' 
GROUP BY ul.user_name 
ORDER BY ug.group_type, ug.user_name;

answered Jun 22, 2014 at 20:13

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cory Harter · Accepted Answer · 2014-06-22 20:14:57Z

0

The way you are joining will give you null values which won't be counted by the count() function.

Can you try doing a Left Join between the two tables using the username column?

SELECT user_name,  group_type, COUNT(*) as number_of_logins
FROM logins.logins LEFT JOIN users.user_groups ON logins.logins.user_name = users.user_groups.username 
WHERE username = user_name and group_type = 'group_2' 
GROUP BY user_name 
ORDER BY group_type, user_name;

answered Jun 22, 2014 at 20:14

Cory Harter

1321 silver badge8 bronze badges

Collectives™ on Stack Overflow

MYSQL select number of elements FOREACH element in another table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related