pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sum(bool)?
10+ messages / 9 participants
[nested] [flat]

* sum(bool)?
@ 2001-02-23 15:22 Olaf Marc Zanger <olaf.zanger@soli-con.com>
  2001-02-26 15:01 ` Re: sum(bool)? Oliver Elphick <olly@lfix.co.uk>
  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

* Re: sum(bool)?
  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>
  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

* sum(bool)?
@ 2001-02-23 15:28 Olaf Zanger <olaf.zanger@soli-con.com>
  2001-02-23 15:43 ` Re: sum(bool)? Tod McQuillin <devin@spamcop.net>
  2001-02-23 15:45 ` sum(bool)? Daniel Wickstrom <danw@rtp.ericsson.se>
  2001-02-23 16:06 ` Re: sum(bool)? Peter Eisentraut <peter_e@gmx.net>
  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: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   ` Re: sum(bool)? 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

* Re: sum(bool)?
  2001-02-23 15:28 sum(bool)? Olaf Zanger <olaf.zanger@soli-con.com>
  2001-02-23 15:43 ` Re: sum(bool)? Tod McQuillin <devin@spamcop.net>
@ 2001-02-23 15:58   ` Olaf Zanger <olaf.zanger@soli-con.com>
  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

* sum(bool)?
  2001-02-23 15:28 sum(bool)? Olaf Zanger <olaf.zanger@soli-con.com>
@ 2001-02-23 15:45 ` Daniel Wickstrom <danw@rtp.ericsson.se>
  2001-02-23 16:48   ` Re: sum(bool)? Andrew Perrin <aperrin@socrates.berkeley.edu>
  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:28 sum(bool)? Olaf Zanger <olaf.zanger@soli-con.com>
  2001-02-23 15:45 ` sum(bool)? Daniel Wickstrom <danw@rtp.ericsson.se>
@ 2001-02-23 16:48   ` Andrew Perrin <aperrin@socrates.berkeley.edu>
  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

* Re: sum(bool)?
  2001-02-23 15:28 sum(bool)? Olaf Zanger <olaf.zanger@soli-con.com>
@ 2001-02-23 16:06 ` Peter Eisentraut <peter_e@gmx.net>
  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

* Fwd: Re: sum(bool)?
@ 2001-02-25 21:26 J.Fernando Moyano <txinete@wanadoo.es>
  2001-02-25 23:54 ` Re: Fwd: Re: sum(bool)? D'Arcy J.M. Cain <darcy@druid.net>
  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 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>
  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


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