Thread: sum-function

Hello,

I like to use udmsearch with postgresql, but for searching I need a
funktion like the following. Every time I get an error. Is there
a possibility to make a work around?

udmsearch=> select sum( dict.word = 'mysql') from dict\g
ERROR:  Unable to select an aggregate function sum(bool)

What does the ERROR mean.

Thanks for any hint.

Christian

P.S. Sorry about the terrible English.
-- Christian Guenther                              Kunsthistoriker Fax: +49 030 4464152             E-mail:
cg@kunst-und-kultur.deTel:+49 030 4442931                http://kunst-und-kultur.de/     Museen, Kuenstler,
Ausstellungen,Kunstprojekte ....
 

guenther@laokoon.IN-Berlin.DE (Christian Guenther) writes:
> udmsearch=> select sum( dict.word = 'mysql') from dict\g
> ERROR:  Unable to select an aggregate function sum(bool)
> What does the ERROR mean.

The '=' operator produces a boolean (true/false) result, but
sum() wants a numeric input.  Postgres is a strongly-typed
system, so it doesn't think booleans are interchangeable
with numerics.

It's been suggested several times that Postgres should
provide a standard conversion function that converts
boolean values to 0/1 numeric values, but no one's gotten
around to making one.  In the meantime you could get the
result that I think you want with something like

select sum(case when dict.word = 'mysql' then 1 else 0 end) ...
        regards, tom lane