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
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
On 07/08/2009 03:33 PM, Gary Smith wrote:
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
could be wrong though -- chime in if you know the definitive answer please).
Anyway, try this and see if it gets you closer.
From: b [my...@logi.ca]
Sent: Wednesday, July 08, 2009 12:55 PM
To: mysql@lists.mysql.com
Subject: Re: COUNT from 2 tables
On 07/08
On 07/08/2009 06:11 PM, Gary Smith wrote:
Create a view or sub select, denormalizing the data and then group it.
select month, sum(login) as num_logins, sum(download) as num_downloads
from
(
select
monthname(s.created) as month_name
, if(ifnull(s.id, 0) 0, 1, 0) as login
, if(ifnull(d.id, 0)