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:
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.
(SELECT COUNT(betid) WHERE result="win" AND customer=b.customer) as wins,
(SELECT COUNT(betid) WHERE result="lose" AND customer=b.customer) as losses
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.
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?
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?