List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 17 2004 7:12am
Subject:Re: COUNT
View as plain text  
In the last episode (May 17), Gustavo Andrade said:
> select count(distinct membros.ID) as total_membros, count(distinct
> replays.ID) as total_replays, count(distinct downloads.ID) as
> total_downloads from membros,replays,downloads;
>  
> if one of the tables have 0 records all the counts will turn to 0
> the count works only if all the tables have records
> how can i fix that?

That's a very inefficient query to start off with; it's generating
membros*replays*downloads records, then removing the dupes from each
column and counting what's left.  If you're running mysql 4.1, this
query will return data instantly (one of the very few examples of where
subselects are much better than joins):

SELECT (SELECT count(*) FROM membros) AS total_membros, 
       (SELECT count(*) FROM replays) AS total_replays, 
       (SELECT count(*) FROM downloads) AS total_downloads;

If you're running 4.0 or older, you'll need to split it up into 4
queries:

SELECT @membros:=count(*) FROM membros;
SELECT @replays:=count(*) FROM replays;
SELECT @downloads:=count(*) FROM downloads;
SELECT @membros AS total_membros, @replays AS total_replays, 
       @downloads AS total_downloads;

Discard the results of the first 3 queries.  If you're running 4.0, you
can join the first three queries into a single UNION query.

-- 
	Dan Nelson
	dnelson@stripped
Thread
COUNTGustavo Andrade17 May
  • Re: COUNTJohn Fawcett17 May
  • Re: COUNTDan Nelson17 May