pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsum(bool)?
10+ messages / 9 participants
[nested] [flat]
* sum(bool)?
@ 2001-02-23 15:22 Olaf Marc Zanger <olaf.zanger@soli-con.com>
0 siblings, 1 reply; 10+ messages in thread
From: Olaf Marc Zanger @ 2001-02-23 15:22 UTC (permalink / raw)
To: pgsql-sql
hi there,
i want to add up the "true" values of a comparison like
sum(a>b)
it just doesn't work like this
any workaround?
it is postgresql 7.0 under linux
thanks
olaf
--
soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger
Lorrainestrasse 23, 3013 Bern / Switzerland
fon:+41-31-332 9782, mob:+41-76-572 9782
mailto:olaf.zanger@soli-con.com, http://www.soli-con.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* sum(bool)?
@ 2001-02-23 15:28 Olaf Zanger <olaf.zanger@soli-con.com>
0 siblings, 3 replies; 10+ messages in thread
From: Olaf Zanger @ 2001-02-23 15:28 UTC (permalink / raw)
To: pgsql-sql
hi there
i'd like to add up the "true" values of a comparison like
sum(a>b)
it just doesn't work like this
any workaround
postgres 7.0 on linux
thanks
olaf
--
soli-con Engineering Zanger
Dipl.-Ing. (FH) Olaf Marc Zanger
Lorrainestrasse 23
3013 Bern / Switzerland
Fon: +41-31-332 9782
Mob: +41-76-572 9782
mailto:info@soli-con.com
mailto:olaf.zanger@soli-con.com
http://www.soli-con.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sum(bool)?
@ 2001-02-23 15:43 Tod McQuillin <devin@spamcop.net>
parent: Olaf Zanger <olaf.zanger@soli-con.com>
2 siblings, 1 reply; 10+ messages in thread
From: Tod McQuillin @ 2001-02-23 15:43 UTC (permalink / raw)
To: Olaf Zanger <olaf.zanger@soli-con.com>; +Cc: pgsql-sql
On Fri, 23 Feb 2001, Olaf Zanger wrote:
> i'd like to add up the "true" values of a comparison like
>
> sum(a>b)
>
> it just doesn't work like this
Try
sum(case when a>b then 1 else 0 end)
--
Tod McQuillin
^ permalink raw reply [nested|flat] 10+ messages in thread
* sum(bool)?
@ 2001-02-23 15:45 Daniel Wickstrom <danw@rtp.ericsson.se>
parent: Olaf Zanger <olaf.zanger@soli-con.com>
2 siblings, 1 reply; 10+ messages in thread
From: Daniel Wickstrom @ 2001-02-23 15:45 UTC (permalink / raw)
To: pgsql-sql
>>>>> "Olaf" == Olaf Zanger <olaf.zanger@soli-con.com> writes:
Olaf> hi there i'd like to add up the "true" values of a
Olaf> comparison like
Olaf> sum(a>b)
Olaf> it just doesn't work like this
Olaf> any workaround
Try using a case statement:
select sum(case when a > b then 1 else 0 end) ....
-Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sum(bool)?
@ 2001-02-23 15:58 Olaf Zanger <olaf.zanger@soli-con.com>
parent: Tod McQuillin <devin@spamcop.net>
0 siblings, 0 replies; 10+ messages in thread
From: Olaf Zanger @ 2001-02-23 15:58 UTC (permalink / raw)
To: Tod McQuillin <devin@spamcop.net>; +Cc: pgsql-sql
hi there,
soooo cool,
this works streight away and took 5 min. waiting for a answer :-)
thanks very much to you tod personal and the mailing list for existence.
Olaf
Tod McQuillin schrieb:
>
> On Fri, 23 Feb 2001, Olaf Zanger wrote:
>
> > i'd like to add up the "true" values of a comparison like
> >
> > sum(a>b)
> >
> > it just doesn't work like this
>
> Try
>
> sum(case when a>b then 1 else 0 end)
> --
> Tod McQuillin
--
soli-con Engineering Zanger
Dipl.-Ing. (FH) Olaf Marc Zanger
Lorrainestrasse 23
3013 Bern / Switzerland
Fon: +41-31-332 9782
Mob: +41-76-572 9782
mailto:info@soli-con.com
mailto:olaf.zanger@soli-con.com
http://www.soli-con.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sum(bool)?
@ 2001-02-23 16:06 Peter Eisentraut <peter_e@gmx.net>
parent: Olaf Zanger <olaf.zanger@soli-con.com>
2 siblings, 0 replies; 10+ messages in thread
From: Peter Eisentraut @ 2001-02-23 16:06 UTC (permalink / raw)
To: Olaf Zanger <olaf.zanger@soli-con.com>; +Cc: pgsql-sql
Olaf Zanger writes:
> i'd like to add up the "true" values of a comparison like
>
> sum(a>b)
sum(case when a>b then 1 else 0 end)
of maybe even just
select count(*) from table where a>b;
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sum(bool)?
@ 2001-02-23 16:48 Andrew Perrin <aperrin@socrates.berkeley.edu>
parent: Daniel Wickstrom <danw@rtp.ericsson.se>
0 siblings, 0 replies; 10+ messages in thread
From: Andrew Perrin @ 2001-02-23 16:48 UTC (permalink / raw)
To: Daniel Wickstrom <danw@rtp.ericsson.se>; +Cc: pgsql-sql
Or how about just:
SELECT count(*) FROM tablename WHERE a > b;
----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
aperrin@socrates.berkeley.edu - aperrin@igc.apc.org
On Fri, 23 Feb 2001, Daniel Wickstrom wrote:
> >>>>> "Olaf" == Olaf Zanger <olaf.zanger@soli-con.com> writes:
>
> Olaf> hi there i'd like to add up the "true" values of a
> Olaf> comparison like
>
> Olaf> sum(a>b)
>
> Olaf> it just doesn't work like this
>
> Olaf> any workaround
>
> Try using a case statement:
>
> select sum(case when a > b then 1 else 0 end) ....
>
>
> -Dan
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Fwd: Re: sum(bool)?
@ 2001-02-25 21:26 J.Fernando Moyano <txinete@wanadoo.es>
0 siblings, 1 reply; 10+ messages in thread
From: J.Fernando Moyano @ 2001-02-25 21:26 UTC (permalink / raw)
To: pgsql-sql
El Viernes 23 Febrero 2001 16:22, escribiste:
> hi there,
>
> i want to add up the "true" values of a comparison like
>
> sum(a>b)
>
> it just doesn't work like this
>
> any workaround?
>
> it is postgresql 7.0 under linux
>
> thanks
>
> olaf
I did exactly the same thing two months ago ....
I created this tiny function:
CREATE FUNCTION bool2int(bool) RETURNS integer
AS 'select (case when $1=true then 1 else 0 end)'
LANGUAGE 'sql';
You can do: sum(bool2int(a>b))
easy ??? ;-))
--
Fernando Moyano
Frase del día:
--------------
El medico me prohibio beber wiski, ahora lo congelo y me lo como!
(*) SymeX ==> http://symex.lantik.com
(*) WDBIL ==> http://wdbil.sourceforge.net
(*) Informate sobre LINUX en http://www.linux.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Fwd: Re: sum(bool)?
@ 2001-02-25 23:54 D'Arcy J.M. Cain <darcy@druid.net>
parent: J.Fernando Moyano <txinete@wanadoo.es>
0 siblings, 0 replies; 10+ messages in thread
From: darcy@druid.net @ 2001-02-25 23:54 UTC (permalink / raw)
To: J.Fernando Moyano <txinete@wanadoo.es>; +Cc: pgsql-sql
Thus spake J.Fernando Moyano
> El Viernes 23 Febrero 2001 16:22, escribiste:
> > i want to add up the "true" values of a comparison like
> >
> > sum(a>b)
> >
> > it just doesn't work like this
> >
> > any workaround?
>
> I did exactly the same thing two months ago ....
>
> I created this tiny function:
>
> CREATE FUNCTION bool2int(bool) RETURNS integer
> AS 'select (case when $1=true then 1 else 0 end)'
> LANGUAGE 'sql';
>
>
> You can do: sum(bool2int(a>b))
>
> easy ??? ;-))
Almost as easy as "SELECT COUNT(*) FROM table WHERE a > b"
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sum(bool)?
@ 2001-02-26 15:01 Oliver Elphick <olly@lfix.co.uk>
parent: Olaf Marc Zanger <olaf.zanger@soli-con.com>
0 siblings, 0 replies; 10+ messages in thread
From: Oliver Elphick @ 2001-02-26 15:01 UTC (permalink / raw)
To: olaf.zanger@soli-con.com; +Cc: pgsql-sql
Olaf Marc Zanger wrote:
>hi there,
>
>i want to add up the "true" values of a comparison like
>
>sum(a>b)
>
>it just doesn't work like this
>
>any workaround?
select count(*) where a > b;
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But God commendeth his love toward us, in that, while
we were yet sinners, Christ died for us."
Romans 5:8
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-02-26 15:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-23 15:22 sum(bool)? Olaf Marc Zanger <olaf.zanger@soli-con.com>
2001-02-26 15:01 ` Oliver Elphick <olly@lfix.co.uk>
2001-02-23 15:28 sum(bool)? Olaf Zanger <olaf.zanger@soli-con.com>
2001-02-23 15:43 ` Tod McQuillin <devin@spamcop.net>
2001-02-23 15:58 ` Olaf Zanger <olaf.zanger@soli-con.com>
2001-02-23 15:45 ` Daniel Wickstrom <danw@rtp.ericsson.se>
2001-02-23 16:48 ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-02-23 16:06 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-25 21:26 Fwd: Re: sum(bool)? J.Fernando Moyano <txinete@wanadoo.es>
2001-02-25 23:54 ` D'Arcy J.M. Cain <darcy@druid.net>
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