pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunion with count?
5+ messages / 4 participants
[nested] [flat]
* union with count?
@ 2007-03-28 14:04 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 2 replies; 5+ messages in thread
From: Gerardo Herzig @ 2007-03-28 14:04 UTC (permalink / raw)
To: pgsql-sql
Hi dudes, i have the following question
i have 2 tables with the same format, and i want to know if is there a
possibility of using some sort of count(*) for achieving this result:
select a from table1 where (condition) union select a from table2 where
(condition), count(a) group by a
The idea is having how many times (condition) is true for both tables.
Im not shure im explaining myself clearly, my english is not the best
(as you can see ;)
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: union with count?
@ 2007-03-28 14:24 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
1 sibling, 1 reply; 5+ messages in thread
From: A. Kretschmer @ 2007-03-28 14:24 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes:
> Hi dudes, i have the following question
> i have 2 tables with the same format, and i want to know if is there a
> possibility of using some sort of count(*) for achieving this result:
> select a from table1 where (condition) union select a from table2 where
> (condition), count(a) group by a
>
> The idea is having how many times (condition) is true for both tables.
>
> Im not shure im explaining myself clearly, my english is not the best
> (as you can see ;)
You can use a subselect, a simple example:
test=# select *, count(1) from (select 1 union select 2 union select 3) foo group by 1;
?column? | count
----------+-------
1 | 1
2 | 1
3 | 1
(3 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: union with count?
@ 2007-03-28 14:27 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Broersma Jr @ 2007-03-28 14:27 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; pgsql-sql
--- Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
> Hi dudes, i have the following question
> i have 2 tables with the same format, and i want to know if is there a
> possibility of using some sort of count(*) for achieving this result:
> select a from table1 where (condition) union select a from table2 where
> (condition), count(a) group by a
I think you are showing count(a) in the wrong spot in your example. But if I understand you
correctly the following should do what you want:
SELECT count(a)
FROM
(
SELECT a
FROM table1
WHERE (your_condition = true)
UNION -- you can use UNION ALL if you
-- want rows that are duplicates from each table.
SELECT a
FROM table2
WHERE (your_condition = true)
)
GROUP BY a;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: union with count?
@ 2007-03-28 15:23 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gerardo Herzig @ 2007-03-28 15:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: andreas.kretschmer@schollglas.com
Thanks! But now i have another problem related with count():
select page_id, word, word_position, count(page_id) from (select * from
search_word('word1', 'table1') union search_word('word2', 'table2')) foo
group by page_id;
and gives me "foo.word must appear in GROUP clause or be used in an
aggregate function"
And i want to group by page_id only, because that is what i need to
count. Tips here?
Thanks again man.
Gerardo
>am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes:
>
>
>>Hi dudes, i have the following question
>>i have 2 tables with the same format, and i want to know if is there a
>>possibility of using some sort of count(*) for achieving this result:
>>select a from table1 where (condition) union select a from table2 where
>>(condition), count(a) group by a
>>
>>The idea is having how many times (condition) is true for both tables.
>>
>>Im not shure im explaining myself clearly, my english is not the best
>>(as you can see ;)
>>
>>
>
>You can use a subselect, a simple example:
>
>test=# select *, count(1) from (select 1 union select 2 union select 3) foo group by 1;
> ?column? | count
>----------+-------
> 1 | 1
> 2 | 1
> 3 | 1
>(3 rows)
>
>
>Andreas
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: union with count?
@ 2007-03-28 16:19 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 5+ messages in thread
From: Andreas Kretschmer @ 2007-03-28 16:19 UTC (permalink / raw)
To: pgsql-sql
Gerardo Herzig <gherzig@fmed.uba.ar> schrieb:
> Thanks! But now i have another problem related with count():
>
> select page_id, word, word_position, count(page_id) from (select * from
> search_word('word1', 'table1') union search_word('word2', 'table2')) foo
> group by page_id;
>
> and gives me "foo.word must appear in GROUP clause or be used in an
> aggregate function"
>
> And i want to group by page_id only, because that is what i need to count.
> Tips here?
You can't aggregate without grouping the not-aggregated columns.
Expand your 'group by' with word and word_position.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-28 16:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-28 14:04 union with count? Gerardo Herzig <gherzig@fmed.uba.ar>
2007-03-28 14:24 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-28 15:23 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-03-28 16:19 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-03-28 14:27 ` Richard Broersma Jr <rabroersma@yahoo.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