Thanks a heap! Yes, it was UNION I was trying (and failing) to
conceptualize. I always forget about that one.
But, I came up with another solution. First, I needed to adjust the
query to better work across several years. The MySQL manual mentions
using partial dates, eg. '2009-07-00' where a more precise date is
unknown or unnecessary. I need to group by month AND year so I changed
MONTHNAME() to:
DATE(CONCAT_WS('-', YEAR(in_date), MONTH(in_date), '01'))
I created a function:
CREATE FUNCTION YEARMONTH (in_date DATE) RETURNS DATE LANGUAGE SQL
BEGIN
RETURN DATE(CONCAT_WS('-', YEAR(in_date), MONTH(in_date), '01'));
END
Then I used a correlated subquery:
SELECT
COUNT(*) AS num_logins,
YEARMONTH(l.created) AS month,
(SELECT
COUNT(*) FROM downloads AS d
WHERE YEARMONTH(d.created) = month
) AS num_downloads
FROM logins AS l
GROUP BY month
ORDER BY month DESC;
Thanks again for the hint.
On Sun, Jul 12, 2009 at 7:50 PM, Martin Radosta<[email protected]> wrote:
> On 07/12/2009 07:16 PM, Martin Radosta wrote:
>
> On 07/11/2009 02:06 PM, brian wrote:
>
> I have a StatsController with which I'd like to make a summary of all
> logins and all downloads for each month.
>
> class Login extends AppModel
> {
> public $belongsTo = array('User');
> public $hasMany = array('Download');
> }
>
> class Download extends AppModel
> {
> public $belongsTo = array(
> 'ItemFile', 'User', 'Login'
> );
> }
>
> I can easily create separate queries but would need to merge them. i
> haven't been able to figure that part out.
>
> public function admin_summary()
> {
> $logins = $this->Login->find(
> 'all',
> array(
> 'fields' => array(
> 'MONTHNAME(created) AS month',
> 'COUNT(*) AS num_logins'
> ),
> 'recursive' => -1,
> 'group' => array('month')
> )
> );
> $downloads = $this->Download->find(
> 'all',
> array(
> 'fields' => array(
> 'MONTHNAME(created) AS month',
> 'COUNT(*) AS num_downloads'
> ),
> 'recursive' => -1,
> 'group' => array('month')
> )
> );
> // ...
> }
>
> This gives me:
>
> Array
> (
> [0] => Array
> (
> [Login] => Array
> (
> [month] => July
> [num_logins] => 16
> )
> )
> [1] => Array
> (
> [Login] => Array
> (
> [month] => June
> [num_logins] => 168
> )
> )
> [2] => Array
> (
> [Login] => Array
> (
> [month] => May
> [num_logins] => 64
> )
> )
> )
>
>
> app/controllers/stats_controller.php (line 171)
>
> Array
> (
> [0] => Array
> (
> [Download] => Array
> (
> [month] => July
> [num_downloads] => 11
> )
> )
> [1] => Array
> (
> [Download] => Array
> (
> [month] => June
> [num_downloads] => 367
> )
> )
> [2] => Array
> (
> [Download] => Array
> (
> [month] => May
> [num_downloads] => 15
> )
> )
> )
>
> What I'm after is something like:
>
> Array
> (
> [0] => Array
> (
> 'July' => array(
> 'Download' => Array
> (
> 'num_downloads' => 11
> ),
> 'Login' => Array
> (
> 'num_logins' => 16
> )
> )
> )
> ...
> )
>
> or even ...
>
> Array
> (
> [0] => Array
> (
> 'July' => array(
> 'Download' => 11,
> 'Login' => 16
> )
> )
> ...
> )
>
>
> Incidentally, I've been tryng to figure out a way to do this in a
> single query (MySQL) but I get strange results:
>
> mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM
> logins GROUP BY month;
> +-------+------------+
> | month | num_logins |
> +-------+------------+
> | July | 15 |
> | June | 168 |
> | May | 64 |
> +-------+------------+
> 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 |
> +-------+---------------+
> | July | 11 |
> | June | 367 |
> | May | 15 |
> +-------+---------------+
> 3 rows in set (0.00 sec)
>
> mysql> SELECT MONTHNAME(l.created) AS month, COUNT(l.id) AS
> num_logins, COUNT(d.id) AS num_downloads FROM logins AS l LEFT JOIN
> downloads AS d ON d.login_id = l.id GROUP BY month;
> +-------+------------+---------------+
> | month | num_logins | num_downloads |
> +-------+------------+---------------+
> | July | 20 | 11 |
> | June | 477 | 367 |
> | May | 71 | 15 |
> +-------+------------+---------------+
> 3 rows in set (0.23 sec)
>
> I figure it's because month is related to logins, not downloads.
> However, it seems odd that it's the login numbers that are wonky, not
> downloads. If anyone can think of a way to do this in one query I'd
> really appreciate it.
>
>
>
>
>
> Try this and let me know if it works:
>
> SELECT MONTHNAME(l.created) AS month, COUNT(l.id) AS
> num_logins, COUNT(d.id) AS num_downloads FROM logins AS l INNER JOIN
> downloads AS d ON d.login_id = l.id GROUP BY month;
>
>
>
>
> Sorry, did'nt read your problem in detail, try this query:
>
> SELECT MONTH , min( num_downloads ) AS num_downloads, min( num_logins ) AS
> num_logins
> FROM (
> SELECT MONTHNAME( created ) AS
> MONTH , COUNT( 1 ) AS num_downloads, NULL AS num_logins
> FROM downloads
> GROUP BY MONTH
> UNION
> SELECT MONTHNAME( created ) AS
> MONTH , NULL AS num_downloads, COUNT( 1 ) AS num_logins
> FROM logins
> GROUP BY MONTH
> ) AS q
> GROUP BY MONTH
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
