Re: count(1) return 0? - Mailing list pgsql-general

On Mon, 01 Mar 2004 12:13:07 +1100
Klint Gore <kg@kgb.une.edu.au> wrote:

> On Mon, 1 Mar 2004 02:14:56 +0200, Zak McGregor <zak@mighty.co.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
========================================================================