List:General Discussion« Previous MessageNext Message »
From:Gary Smith Date:July 8 2009 7:33pm
Subject:RE: COUNT from 2 tables
View as plain text  
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 [mysql@stripped]
Sent: Wednesday, July 08, 2009 12:21 PM
To: mysql@stripped
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=1
Thread
COUNT from 2 tablesb8 Jul
  • RE: COUNT from 2 tablesGary Smith8 Jul
    • Re: COUNT from 2 tablesb8 Jul
      • RE: COUNT from 2 tablesGary Smith9 Jul
        • Re: COUNT from 2 tablesb9 Jul
  • Replication switch Master to slave and backBryan Cantwell8 Jul
    • Re: Replication switch Master to slave and backAaron Blew8 Jul
      • Re: Replication switch Master to slave and backMarcus Bointon8 Jul