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.

Village Idiot · 07-21-2009, 08:36 PM

Use 0 and 1 instead of "win" and "loose". Numbers are better for machines to parse. I would also use IDs for customers instead of names, attach a related table if you want info on them. I also don't see how a join statement is needed for what you have, join is for adding results from associated tables.

I wrote this off the top of my head, this should be something close to what you need.

Code:

SELECT
    b.group, 
    b.customer, 
    (SELECT COUNT(betid) WHERE result="win" AND customer=b.customer) as wins, 
    (SELECT COUNT(betid) WHERE result="lose" AND customer=b.customer) as losses 
FROM 
    bets b
GROUP BY 
    b.customer
ORDER BY 
    b.group ASC

I'm not fully sure how to do the grouping here, I don't have the time to play with it because I am at work.

Wildhoney · 07-21-2009, 08:51 PM

Building onto Village Idiot's example:

sql Code:

SELECT
    `customer` AS `customer_id`, `group`, `customer`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Win' AND `customer` = `customer_id`) AS `wins`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Lose' AND `customer` = `customer_id`) AS `losses`
FROM
    `results`
GROUP BY
    `customer`
ORDER BY
    `wins`
DESC

captainmerton · 07-21-2009, 09:33 PM

That is pretty much what i'm looking for thanks. Glad this was in the absolute beginners section as that is what i obviously am. Only issue i have is its returning th extra customer_id column which matches customer but its an extra column i dont really want to return. Any way i could adjust the query to return the 4 columns instead of the 5?

Wildhoney · 07-21-2009, 09:50 PM

Naturally!

sql Code:

SELECT
    `group`, `customer`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Win' AND `customer` = `r`.`customer`) AS `wins`,
    (SELECT COUNT(`id`) FROM `results` WHERE `result` = 'Lose' AND `customer` = `r`.`customer`) AS `losses`
FROM
    `results` `r`
GROUP BY
    `customer`
ORDER BY
    `wins`
DESC

captainmerton · 07-22-2009, 03:56 PM

Perfect! Much appreciated.

captainmerton · 07-23-2009, 12:09 PM

Quote:

Use 0 and 1 instead of "win" and "loose". Numbers are better for machines to parse. I would also use IDs for customers instead of names, attach a related table if you want info on them.

Thanks for all your help guys but i wanted to pick up on these earlier comments. I think going with 0 and 1 for wins and losses makes sense. As for using ids instead of character usernames in my table - will this make a big difference. i thought about this at design stage but decided against it as i didnt really need ids as all usernames would be unique and assumed they would just be stored as hex or some kind of binary number value anyway and stored in the db in the most efficent way. Can someone explain whether going with numeric ids and using a userdetail table to get the actual character name would be better as in essence i would be reading 2 tables instead of 1 everytime i wanted a username. But will change if it is more efficient to go with numeric ids. Any thoughts?