"TGOS" <tg**@invalid.invalid> wrote in message
SELECT document.document_id, COUNT(page_id) as pages FROM document INNER
JOIN page ON page.document_id=document.document_id WHERE pages>10 GROUP
BY page_id
But how can I now run a count over the whole thing?
In MySql 4.1 it is just
select count(*) from
(select ...) as whatever_name
In most versions of MySql you can also do
select @var:=0;
select document.id, count(page.id) as pages, @var:=@var+1
from
document
inner join page on document.id = page.document_id
group by document.id
having pages > 1
and the 3rd column @var:= holds the count.
In any version of MySql, you can also always create a new table to store the
results of the sub-query, and populate it with an insert ... select
statement. Internally, this is how MySql would handle some sub-queries. To
create a temporary table visible to only one connection, do the following:
create temporary table_name ...