List:General Discussion« Previous MessageNext Message »
From:René Fournier Date:January 30 2006 10:01pm
Subject:How to restrict this query... (need subquery?)
View as plain text  
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
Thread
How to restrict this query... (need subquery?)René Fournier30 Jan
  • Re: How to restrict this query... (need subquery?)Peter Brawley31 Jan
    • Re: How to restrict this query... (need subquery?)René Fournier31 Jan
  • Re: How to restrict this query... (need subquery?)Rhino31 Jan