Skip site navigation (1) Skip section navigation (2)

Re: count(1) return 0?

From: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
To: Zak McGregor <zak(at)mighty(dot)co(dot)za>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: count(1) return 0?
Date: 2004-03-01 02:12:12
Message-ID: 40429BFC33B.B2CEKG@ (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Mon, 1 Mar 2004 03:27:39 +0200, Zak McGregor <zak(at)mighty(dot)co(dot)za> wrote:
> in fixture 4916, player 1200 won 9-0, but his opponent does not show as having 0
> in the second result set. I suspect what I am expecting is somehow illogical,
> but I can't see why.
> I would expect to see a total of 0 for the case where winner=away and
> fixture=4916 = the fixtures are after all being grouped - if there are zero
> cases where fixture=4916 and winner=away, as far as I can see that should be the
> count() result... however as I said I am probably missing something quite basic.
> If anyone could please explain why what I expect to see is not what I actually
> see I would be very grateful indeed.

The counting and grouping is done after the where clause is applied.

since player iplaybadly (who was 1200's opponent) didnt win any, he/she
is not included in the result set to be grouped and counted.  You need
to get iplaybadly into the result set first.

try something like

select fixture, home, sum(case winner=home then 1 else 0 end) 
>from results
group by fixture, home


: Klint Gore                            : "Non rhyming    :
: EMail   : kg(at)kgb(dot)une(dot)edu(dot)au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :

In response to


pgsql-general by date

Next:From: Zak McGregorDate: 2004-03-01 02:28:31
Subject: Re: count(1) return 0?
Previous:From: Doug McNaughtDate: 2004-03-01 02:07:36
Subject: Re: A simple question (under pressure :-))....

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group