List:General Discussion« Previous MessageNext Message »
From:John Fawcett Date:May 17 2004 6:46am
Subject:Re: COUNT
View as plain text  
From: "Gustavo Andrade"
> 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;

Why join three tables to count the records in each one? I'm sure the
performance will be poor once you get more data.

> 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?

By joining the tables you are asking for all possible combinations of the
rows (cartesian product).
The number of rows obtained is:

(n. rows in table 1) * (n. rows in table 2) * ( n. rows in table 3)

So if a table has 0 rows there are 0 possible combinations.

For this reason and also for performance reasons, you should do 3 separate

If ID is a unique key, you can also take out the distinct, which in your
query you needed because by making all possible combinations you repeated
the same ID many times.


Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em

COUNTGustavo Andrade17 May
  • Re: COUNTJohn Fawcett17 May
  • Re: COUNTDan Nelson17 May