MySQL Forums
Forum List  »  Newbie

Re: Dividing two columns in mysql
Posted by: Ric Gauci
Date: March 07, 2014 05:53PM

Thanks Stefan

Your suggestion didn't work. What the accuracy is supposed to calculate is th % of Goals scored from total scoring shots - Eg. Team A scored 51 goals from 141 scoring shots which s a % of 36.43% so this is the result I am expecting. See full query and output below;



SELECT
Team,
COUNT(Total) AS P,
SUM(Goals) AS GoalsFor,
SUM(Behinds) AS BehindsFor,
SUM(Goals+Behinds) AS ScoringShots,
SUM(Total) AS 'For',
ROUND(AVG(Total),2) AS AveScoreFor,
ROUND(SUM(GOALS/(GOALS+BEHINDS)),2) AS Accuracy

FROM `MatchDetails`
WHERE Season = 1898
GROUP BY Team

This is the output

Team P GoalsFor BehindsFor ScoringShots For AveScoreFor Accuracy
TeamA 16 51 90 141 396 24.75 5.33
TeamB 16 85 166 251 676 42.25 5.48

This is the expected output

Team P GoalsFor BehindsFor ScoringShots For AveScoreFor Accuracy
TeamA 16 51 90 141 396 24.75 36.43
TeamB 16 85 166 251 676 42.25 33.86

Sorry not sure how to make the table look neat on here...

Thanks again

Options: ReplyQuote


Subject
Written By
Posted
March 07, 2014 07:58AM
Re: Dividing two columns in mysql
March 07, 2014 05:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.