mysql JOIN challange

captainmerton · 07-21-2009, 08:05 PM

I have spent the last week trying to create a query that returns the data from one table in the format i want it but without success. I am sure I need to JOIN a table with itself but its the COUNTing of types of row which is proving most challenging. Wonder if anyone can help or even further resolve for me. Its a challange indeed.

Essentially I want a query that will take this table:

Code:

betid 	        customer 	group 	result
34		joebloggs	1	Win
34		another		1	Lose
36		mrjones		1	Win
37		joebloggs	1	Win
38		mrJones		1	Lose
38		joebloggs	1	Lose
40		joebloggs	2	Win

and reformat it in this way counting up wins and losses GROUPing by group, customer and then win and loss counts.

Code:

group		customer	Wins	Losses
1		joebloggs	2	1
1		mrjones		1	1
1		another		0	1
2		joebloggs	1	0

Handling 0 losses is not easy as I am essentially trying to return a count of value for a row that does not exist.