|
Home > Archive > MySQL ODBC Connector > January 2006 > How to restrict this query... (need subquery?)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to restrict this query... (need subquery?)
|
|
| René Fournier 2006-01-30, 8:24 pm |
| Hello,
I have two tables: Accounts and History. Basically, I want to see how =20=
much activity each account has during a given period of time. Even if =20=
an account has no activity, I still want to see it in the result =20
(naturally with zeros or null). In the history table, there is a =20
column called time_sec=97it's a UNIX timestamp. That is the column =20
needed to restrict the counting to a particular day or month. My =20
problem is that either I get all the accounts (good) without =20
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 =3D 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 =20
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 =3D 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 =20
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...sie.nctu.edu.tw
| |
| Peter Brawley 2006-01-30, 8:24 pm |
| 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=...y@earthlink.net
>
>
>
> --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/mysql? unsub...sie.nctu.edu.tw
| |
|
| 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@renefournier.com>
To: <mysql@lists.mysql.com>
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...ati
co.ca
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| René Fournier 2006-01-30, 8:24 pm |
| Hi Peter,
Yes, after I posted the question, I did some more reading and learned =20=
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 =3D accounts.id AND =20
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=E9
>
>
> Did you try moving your WHERE condition to the ON clause?
>
> PB
>
> -----
>
> Ren=E9 Fournier wrote:
[color=darkred]
>
>
> --=20
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: =20
> 1/27/2006
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|