List:General Discussion« Previous MessageNext Message »
From:Rhino Date:January 30 2006 11:17pm
Subject:Re: How to restrict this query... (need subquery?)
View as plain text  
Rene,

The count(*) function should always report the exact number of rows that 
satisfy the query. If the query has only a WHERE clause, count(*) should 
report the number of rows that satisfied the WHERE. If the query has a only 
a GROUP BY, count(*) should report the number of groups found by the query. 
If the query has WHERE _and_ GROUP BY, count(*) should report the number of 
groups that were found after the WHERE clause had been applied to the data 
in the table.

Would that help you?

Rhino

----- Original Message ----- 
From: "René Fournier" <m5@stripped>
To: <mysql@stripped>
Sent: Monday, January 30, 2006 5:01 PM
Subject: How to restrict this query... (need subquery?)


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=1


-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 27/01/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: 27/01/2006

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