Hi Peter,
Yes, after I posted the question, I did some more reading and learned
that it's possible to have several conditions in the ON clause. I tried:
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 AND
history.time_sec BETWEEN 1138604400 AND 1138652381
GROUP BY accounts.id, history.msg_src
ORDER BY accounts.id DESC, history.msg_src ASC
...and it does exactly what I need. Thanks for the reply though.
...Rene
On 30-Jan-06, at 4:12 PM, Peter Brawley wrote:
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?
[EMAIL PROTECTED]
--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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
