Thread: union with count?

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

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

--- 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;

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
>  
>

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°