Re: count(1) return 0?

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
========================================================================