I'm trying to get a count for columns in 2 tables at once. I have
sessions and downloads tables and would like to get some basic stats.
mysql> describe sessions;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> describe downloads;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| created | datetime | YES | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| item_file_id | int(10) unsigned | NO | MUL | NULL | |
| session_id | int(10) unsigned | NO | | NULL |
|
| path | text | NO | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM
sessions GROUP BY month;
+-------+------------+
| month | num_logins |
+-------+------------+
| July | 6 |
| June | 214 |
| May | 150 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads
FROM downloads GROUP BY month;
+-------+---------------+
| month | num_downloads |
+-------+---------------+
| June | 389 |
| May | 220 |
+-------+---------------+
2 rows in set (0.01 sec)
In trying to get the count from both tables at once, the logins are no
longer being summed as expected:
mysql> SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins,
COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id = s.id GROUP BY month;
+-------+------------+---------------+
| month | num_logins | num_downloads |
+-------+------------+---------------+
| July | 6 | 0 |
| June | 539 | 389 |
| May | 350 | 220 |
+-------+------------+---------------+
3 rows in set (0.31 sec)
Is this possible to do without using a sub-query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
