TalkPHP - View Single Post

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.

07-21-2009, 08:36 PM	#2 (permalink)
Village Idiot Wizard Join Date: Sep 2007 Posts: 1,299 Thanks: 17	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. __________________