From: Peter Brawley
Date: January 30 2006 11:12pm
Subject: Re: How to restrict this query... (need subquery?)
List-Archive: http://lists.mysql.com/mysql/194374
Message-Id: <43DE9D46.2040405@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

René

 >What I need to do, somehow, is apply that WHERE clause
 >to the COUNT part of the SELECT. Any ideas?

Did you try moving your WHERE condition to the ON clause?

PB

-----

René Fournier wrote:
> Hello,
>
> I have two tables: Accounts and History. Basically, I want to see how 
> much activity each account has during a given period of time. Even if 
> an account has no activity, I still want to see it in the result 
> (naturally with zeros or null). In the history table, there is a 
> column called time_sec—it's a UNIX timestamp. That is the column 
> needed to restrict the counting to a particular day or month. My 
> problem is that either I get all the accounts (good) without 
> restricting to a day or month (bad)...
>
> SELECT
>     accounts.id,
>     accounts.account_name,
>     accounts.company_name,
>     history.msg_src,
>     COUNT(history.msg_src) as msg_num
> FROM accounts
>     LEFT JOIN history ON history.account_id = accounts.id
> GROUP BY accounts.id, msg_src
> ORDER BY accounts.id DESC, history.msg_src ASC
>
> ... or I get a result that is restricted (good), but without showing 
> all the accounts (bad)...
>
> SELECT
>     accounts.id,
>     accounts.account_name,
>     accounts.company_name,
>     history.msg_src,
>     COUNT(history.msg_src) as msg_num
> FROM accounts
>     LEFT JOIN history ON history.account_id = accounts.id
> WHERE
>     history.time_sec > 1138604400 AND history.time_sec < 1138652381
> GROUP BY accounts.id, msg_src
> ORDER BY accounts.id DESC, history.msg_src ASC
>
> What I need to do, somehow, is apply that WHERE clause to the COUNT 
> part of the SELECT. Any ideas?
>
> ...Rene
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=peter.brawley@stripped
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 
> 1/27/2006
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006