pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving the sum of two queries
5+ messages / 4 participants
[nested] [flat]
* Having the sum of two queries
@ 2007-07-16 11:06 Dani Castaños <danitao.mailists@gmail.com>
2007-07-16 11:15 ` Re: Having the sum of two queries Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-07-16 11:57 ` Re: Having the sum of two queries Roberto Spier <roberto.spier@conab.gov.br>
2007-07-16 22:00 ` Re: Having the sum of two queries Hélder M. Vieira <hmv@mail.telepac.pt>
0 siblings, 3 replies; 5+ messages in thread
From: Dani Castaños @ 2007-07-16 11:06 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Having the sum of two queries
2007-07-16 11:06 Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
@ 2007-07-16 11:15 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2007-07-16 11:15 UTC (permalink / raw)
To: pgsql-sql
Στις Δευτέρα 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Having the sum of two queries
2007-07-16 11:06 Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
@ 2007-07-16 11:57 ` Roberto Spier <roberto.spier@conab.gov.br>
2 siblings, 0 replies; 5+ messages in thread
From: Roberto Spier @ 2007-07-16 11:57 UTC (permalink / raw)
To: Dani Castaños <danitao.mailists@gmail.com>; +Cc: pgsql-sql
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Having the sum of two queries
2007-07-16 11:06 Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
@ 2007-07-16 22:00 ` Hélder M. Vieira <hmv@mail.telepac.pt>
2007-07-17 07:14 ` Re: Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Hélder M. Vieira @ 2007-07-16 22:00 UTC (permalink / raw)
To: pgsql-sql
>... 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Having the sum of two queries
2007-07-16 11:06 Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
2007-07-16 22:00 ` Re: Having the sum of two queries Hélder M. Vieira <hmv@mail.telepac.pt>
@ 2007-07-17 07:14 ` Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dani Castaños @ 2007-07-17 07:14 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-17 07:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-16 11:06 Having the sum of two queries Dani Castaños <danitao.mailists@gmail.com>
2007-07-16 11:15 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-07-16 11:57 ` Roberto Spier <roberto.spier@conab.gov.br>
2007-07-16 22:00 ` Hélder M. Vieira <hmv@mail.telepac.pt>
2007-07-17 07:14 ` Dani Castaños <danitao.mailists@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox