Re: COUNT

[John Fawcett]

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
selects.

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.

John

_________________________________________________
Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em
http://www.arena-star.com.br/forum/