Re: [SQL] union with count?

2007-03-28 Thread A. Kretschmer 
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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] union with count?

2007-03-28 Thread Gerardo Herzig 

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
 




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] union with count?

2007-03-28 Thread Andreas Kretschmer 
Gerardo Herzig [EMAIL PROTECTED] 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°

---(end of broadcast)---
TIP 6: explain analyze is your friend