Canman2005 Posted November 28, 2008 Share Posted November 28, 2008 Hi I have a few questions, but i'll take it one at a time, firstly, have a table called `people` which looks like name score David 150 Bryan 200 David 300 Susan 120 At the moment i'm using SELECT MAX(score) FROM `people` with that data it gives me 300 at the moment. I want to do the same Query but count duplicates with the same `name`, so if I did this Query then it would return the value 450 as David has 2 entries and added together they eual the highest value. Any help would be ace Thanks Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 SELECT name, SUM(score) AS totalScore FROM people GROUP BY name ORDER BY totalScore DESC Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 Cool thanks man, have another question along the same lines Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 Similar to the last question, but I have this QUERY SELECT name, score*100/$max as pc FROM `people` ORDER BY score DESC Which basically returns David 300 Bryan 200 David 150 Susan 120 How can I do the same and count the total score for anyone with the same `name` so the result for that data would be David 450 Bryan 200 Susan 120 But it would need to retain the part of the Query score*100/$max as pc as that is used later on in the code to work out an average score, but I need to try and group anyone with the same `name` together but adding up their `score` sorry its a bit vague any help would be wicked thanks Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 Not sure if I follow: SELECT name, SUM(score) AS totalScore, MAX(score) AS maximumScore, SUM(score)/MAX(score) * 100 AS pc FROM people GROUP BY name ORDER BY totalScore DESC Does that help, even if it doesn't give exactly what you want Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 I think that has done is Mark, thanks mate Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 Just one question, where in the QUERY would I define the $max value? Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 Just one question, where in the QUERY would I define the $max value? if $max is already pre-defined (and you're sure it is a numeric value): $query = 'SELECT name, SUM(score) AS totalScore, MAX(score) AS maximumScore, SUM(score)/MAX(score) * 100 AS pc1, 100 * SUM(score)/'.$max.' AS pc FROM people GROUP BY name ORDER BY totalScore DESC'; Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 I only ask because before the pc value was coming back at around 85.43 65.21 34.54 and now it's being returned as 171.6049 145.4324 90.6655 I need to try and get the pc score to be 100 or less than 100 any ideas how I can? Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 SELECT name, SUM(score) AS totalScore, SUM(score) / ( SELECT SUM(score) FROM people) AS pc FROM people GROUP BY name ORDER BY totalScore DESC Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 hummm, that didnt seem to work either, so you know my code, I have the following $maxres = mysql_query ("SELECT SUM(score) AS totalScore FROM people GROUP BY name ORDER BY totalScore DESC LIMIT 1"); Which works perfect, then I have the following $max = mysql_result ($maxres,0); $sql = "SELECT name, score, score*100/$max as pc FROM people ORDER BY score DESC LIMIT 10"; $res = mysql_query($sql); while (list($name, $score, $pc) = mysql_fetch_row($res)) { printf('% (%0.1f<br>', $score, $pc/10); } Which works and currently goes and returns everything in `score` order with a point score going from 0 0.1 0.2 ......... 9.8 9.9 10.0 But what I want to do is to get it to group up anyone with the same `name` but adding their `scores` up and then returning a point score. Does that help at all? Thanks very much for all the help so far Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 Still majorly stuck, is there a way to do this or am I trying to achive the impossible Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 [quote author=Canman2005 link=topic=227570.msg1050532#msg1050532 date=1227883235] What does this give you? [code=php:0] $maxres = mysql_query ("SELECT SUM(score) AS totalScore FROM people"); $max = mysql_result ($maxres,0); $sql = "SELECT name, SUM(score) AS totalScore, SUM(score) *100/$max as pc FROM people ORDER BY totalScore DESC LIMIT 10"; $res = mysql_query($sql); while (list($name, $totalScore, $pc) = mysql_fetch_row($res)) { printf('%0.1',f$pc); echo ' - '.$name.' - '.$totalScore.'<br>'; } Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 I get #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 mea culpa $maxres = mysql_query ("SELECT SUM(score) AS totalScore FROM people"); $max = mysql_result ($maxres,0); $sql = "SELECT name, SUM(score) AS totalScore, SUM(score) *100/$max as pc FROM people GROUP BY name ORDER BY totalScore DESC LIMIT 10"; $res = mysql_query($sql); while (list($name, $totalScore, $pc) = mysql_fetch_row($res)) { printf('%0.1',f$pc); echo ' - '.$name.' - '.$totalScore.'<br>'; } Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted November 28, 2008 Author Share Posted November 28, 2008 Still doesnt seem to return correct, the pc score should return within the value of 100 and I have some returning as 180.7775 for example Should I give up? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.