Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) > 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month
________________________________________ From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:21 PM To: mysql@lists.mysql.com Subject: COUNT from 2 tables 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=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org