Re: count(1) return 0?

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.

+---------------------------------------+-----------------+
: 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 : :
+---------------------------------------+-----------------+