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

Re: count(1) return 0?

From: Zak McGregor <zak(at)mighty(dot)co(dot)za>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: count(1) return 0?
Date: 2004-03-01 01:27:39
Message-ID: 20040301032739.42c0bee5.zak@mighty.co.za (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, 01 Mar 2004 12:13:07 +1100
Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> wrote:

> On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <zak(at)mighty(dot)co(dot)za> wrote:
> > to return a 0 value instead of absolutely nothing if no rows match
> > fixture=4916 and winner=away? I get absolutely no results at all.
> > 
> > any ideas please?
> 
> dont group by winner.  it's not returned in the statement so it's not
> needed anyway.  an exact value is specified in the where clause so it's
> not going to be different either.

Thanks Klint, that works. I will need to group by fixture though (not winner -
that was just one of the permutations that I was playing around with) at some
point which presents the same problem. I have a number of fixtures in the
results table which need to be aggregated to tally frames won per player per
fixture (stored as a view) and those results further aggregated to find points
for a points table. All is good until a player has won no frames in a fixture,
at which point the fact that an empty result is returned becomes problematic.

For instance:

select fixture, count(1) as total, away from results where winner=away group by
fixture, away;
 fixture | total | away 
---------+-------+------
    4913 |     4 | 1214
    4916 |     9 | 1200
    4918 |     7 | 1123
    4928 |     9 | 1318
    4935 |     5 | 1265
(5 rows)

select fixture, count(1) as total, home from results where winner=home group by
fixture, home;
 fixture | total | home 
---------+-------+------
    4913 |     9 | 1198
    4918 |     9 | 1257
    4928 |     1 | 1401
    4935 |     9 | 1359
(4 rows)

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.

Thanks

Ciao

Zak

--
========================================================================
http://www.carfolio.com/        Searchable database of 10 000+ car specs
========================================================================

In response to

Responses

pgsql-general by date

Next:From: Uwe C. SchroederDate: 2004-03-01 01:33:11
Subject: Re: count(1) return 0?
Previous:From: Kris JurkaDate: 2004-03-01 01:21:24
Subject: Re: count(1) return 0?

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