count

[higgins]

i\'m doing hard on a COUNT SELECT:

 

in a table STATS there are the following columns:

 

sid id wlt

 

\'sid\' is the key (integer value), \'id\' the player\'s number (integer value) and \'wlt\' describes if the player won, lost or tied a game (character either \'w\', \'l\' or \'t\').

 

the following entries are in the table STATS e.g:

 

sid_id_wlt

----------

04 79 t

11 31 w

16 31 w

18 31 w

21 31 w

30 31 w

35 31 w

37 31 w

39 79 t

43 31 w

49 31 w

56 79 w

74 31 w

83 85 l

99 85 t

 

i\'m trying to get counts/sums for the distinct players 31, 79 and 85 within one query, like:

 

id__w__l__t

------------

31 10 00 00

79 01 00 02

85 00 01 01

 

i use:

SELECT DISTINCT stats.id,IF(stats.wlt = \'W\', count(*), 0) AS W,IF(stats.wlt = \'L\', count(wlt), 0) AS L,IF(stats.wlt = \'T\', count(wlt), 0) AS T FROM stats group by stats.id, stats.wlt

 

but always i get something like this:

id__w__l__t

----------

85 00 00 01 

79 00 00 02 

85 00 01 00 

79 01 00 00 

31 10 00 00

 

anyone has a clue how to fix this prob? i\'d really appreciate it -

thanx higgins