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 <za*@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
================================================== ======================
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
ma*******@postgresql.org so that your
message can get through to the mailing list cleanly