Hi

Currently I have a statement uniting the count of different alike
tables;

( SELECT COUNT( DISTINCT user) FROM users_a WHERE sex='m' ) UNION (
SELECT COUNT( DISTINCT user ) FROM users_b WHERE sex = 'm' )

But this returns two rows that I have to sum up - it would be nice if
it was possible to SUM the counts with the sql instead. Something like

( SELECT COUNT(...) ... ) SUM ( SELECT COUNT(...) ... )

Returning only one row.

I'm running mysql 4.0.18 and php.

Thanx

On 24 Jan, 10:31, "ask" <ask...@gmail.com> wrote:
> Hi
>
> Currently I have a statement uniting the count of different alike
> tables;
>
> ( SELECT COUNT( DISTINCT user) FROM users_a WHERE sex='m' ) UNION (
> SELECT COUNT( DISTINCT user ) FROM users_b WHERE sex = 'm' )
>
> But this returns two rows that I have to sum up - it would be nice if
> it was possible to SUM the counts with the sql instead. Something like
>
> ( SELECT COUNT(...) ... ) SUM ( SELECT COUNT(...) ... )
>
> Returning only one row.
>
> I'm running mysql 4.0.18 and php.
>
> Thanx

Well I hate to disappoint you, but your query only produces 2 rows as
long as both counts are different. If the count of distinct users from
users_a was exactly the same as the count of distinct users from user_b
then you would only get 1 row. To always get both you must use UNION
ALL.

Now down to your question:

The following query does what you ask. But if the same user is in both
user_a and user_b then they will be counted twice. Is this what you
want? If you want a count of distinct names from the combined tables,
then the second query below does that (no need for DISTINCTs as the
UNION keyword takes care of that).

First Query:

SELECT SUM( c_user )
FROM (
(

SELECT COUNT( DISTINCT user ) c_user
FROM `user_a`
)
UNION ALL (

SELECT COUNT( DISTINCT user )
FROM `user_b`
)
) AS ac

Second Query:

SELECT count( user )
FROM (
(

SELECT user
FROM `user_a`
)
UNION (

SELECT user
FROM `user_b`
)
) AS ac