Thread: Having the sum of two queries

Hi all!

I want to do something like this:

SELECT status, COUNT( status ) AS total
FROM users
GROUP BY status

UNION

SELECT status, COUNT(status) AS total
FROM imported_users
GROUP BY status


And have the result of both added.

I've tried something like

SELECT tot.status, COUNT(total)
FROM( QUERY A UNION QUERY B ) AS tot
GROUP BY tot.status

But it doesn't works. It doesn't add the total columns with the same 
status...

Στις Δευτέρα 16 Ιούλιος 2007 14:06, ο/η Dani Castaños έγραψε:
> Hi all!
>
> I want to do something like this:
>
> SELECT status, COUNT( status ) AS total
> FROM users
> GROUP BY status
>
> UNION
>
> SELECT status, COUNT(status) AS total
> FROM imported_users
> GROUP BY status
>
>
> And have the result of both added.
>
> I've tried something like
>
> SELECT tot.status, COUNT(total)
> FROM( QUERY A UNION QUERY B ) AS tot
> GROUP BY tot.status
>
> But it doesn't works. It doesn't add the total columns with the same
> status...

It should also be UNION ALL, just in case some status'es are common in the 2
subqueries.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate

--
Achilleas Mantzios

Dani Castaños escreveu:

...
>
> And have the result of both added.
>
> I've tried something like
>
> SELECT tot.status, COUNT(total)
Should be

SELECT tot.status, SUM(total)
> FROM( QUERY A UNION QUERY B ) AS tot
> GROUP BY tot.status
>
> But it doesn't works. It doesn't add the total columns with the same 
> status...

>... SELECT tot.status, COUNT(total)
> FROM( QUERY A UNION QUERY B ) AS tot
> GROUP BY tot.status
>
> But it doesn't works. It doesn't add the total columns with the same 
> status...
>...


I guess you have two problems, if I understand what you're trying to do.
- As Roberto Spier noted, 'sum' would be better than 'count' in the outer 
query.
- Also, 'union' will silently remove duplicate rows, unless you use 'union 
all'.

Please check if this is what you want:

select status, count(status)
from
(
select status from users
union all
select status from imported_users
) as fff
group by status;


Regards,
´
Helder M. Vieira 

Thank you all!

My problem has been solved in another way... because the inner queries 
had left outer joins and so on, and I finally have had to do two queries 
and treat results by PHP.
Again... thank you all!