Re: [Hampshire] Help with SQL COUNT() statement.

Hugo Mills wrote:
> On Wed, Aug 22, 2007 at 12:59:59PM +0100, David Ramsden wrote:
>> Initially I thought the following SQL statement would work:
>>
>> SELECT COUNT(accesslog.url) AS requests, COUNT(deniedlog.url) AS denied
>> FROM accesslog, deniedlog;
>>
>> But this gives me:
>>
>> +----------+--------+
>> | requests | denied |
>> +----------+--------+
>> | 117500 | 117500 |
>> +----------+--------+
>> 1 row in set (0.00 sec)
>

>    As pointed out, this is the full cross product -- the database
> generates a row for each pair of (row from accesslog, row from
> deniedlog), and then performs the count on those.

Thanks for all the replies. I didn't know how SQL treated that
statement. I've done some research and have a better understanding.

>
> [snip]
>> Can anyone help me out? I don't want to execute two seperate statements
>> if possible.
>

>    Why not? It's not going to reduce the load on the server to do it
> in one query -- in fact, I'd say it'll be [marginally] less load to do
> two separate queries, regardless of how you do it.

>

The reason I wanted one statement is so that I don't need to change my
PHP function that generates graphs. At the moment it expects two columns
but if I can't get a SQL statement to do what I want, I'll change the
function.

James' UNION SELECT is nearly there but as pointed out only gives one
column with two rows. I might give in and change my PHP graph function
or create a specific one just for this task :-)

Regards,
David.
-- 
 .''`.     David Ramsden
: :'  :    http://0wned.it/
`. `'`     PGP key ID: 3454B217 on wwwkeys.eu.pgp.net
  `-  Debian - Because it works (tm).