Re: Using union and count

On 5 Feb 2004 09:51:06 -0800, jiten_vaja_at_bigfoot.com (Jiten) wrote:

>I need some help, I'm trying to query a distributed database to count
>a number of rows and give me a total, i've got the following code, i'm
>using sql in oracle:
>
>SELECT COUNT(Opponent) FROM databaseA_at_databaseAlink WHERE Opponent =
>'A' AND Date > SYSDATE UNION
>SELECT COUNT(Opponent) FROM databaseB_at_databaseBlink WHERE Opponent =
>'B' AND Date > SYSDATE UNION
>SELECT COUNT(Opponent) FROM databaseC_at_databaseClink WHERE Opponent =
>'C' AND Date > SYSDATE UNION
>SELECT COUNT(Opponent) FROM databaseD_at_databaseDlink WHERE Opponent =
>'D' AND Date > SYSDATE;
>
>When this is run I get a Opponent with a count of 0 and 1.
>
>Any help is much appreciated

UNION will only return distinct values.
UNION ALL will return all values.

By design your individual query parts either return 0 or 1, because you only check for 1 value.

--
Sybrand Bakker, Senior Oracle DBA