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

On 22/08/07, David Ramsden <david@???> wrote:

> Hi all,
>
> I've got two tables in my database. One called "accesslog" and one
> called "deniedlog". They contain information from Squid and squidGuard.
> I'm trying to extract the number of requests from the accesslog and the
> number of requests from the denied log so I can display some stats.
>
> Initially I thought the following SQL statement would work:
>
> SELECT COUNT(accesslog.url) AS requests, COUNT(deniedlog.url) AS denied
> FROM accesslog, deniedlog;


[snip]

You're doing a cartesian product of the tables, rather than a union. Try


SELECT COUNT(accesslog.url) from accesslog
UNION SELECT COUNT(deniedlog.url) FROM deniedlog;

instead

Which gives you two rows, rather than two columns - I suspect there's a way
to get two columns with subqueries, but I'd have to think about it more than
I can at the moment

James