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