Getting row with highest value

[Canman2005]

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

 

[Mark Baker]

SELECT name,
       SUM(score) AS totalScore
  FROM people
GROUP BY name
ORDER BY totalScore DESC

[Canman2005]

Cool thanks man, have another question along the same lines

[Canman2005]

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

[Mark Baker]

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

[Canman2005]

I think that has done is Mark, thanks mate

[Canman2005]

Just one question, where in the QUERY would I define the $max value?

[Mark Baker]

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';

[Canman2005]

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?

[Mark Baker]

SELECT name,
       SUM(score) AS totalScore,
       SUM(score) / ( SELECT SUM(score)
                        FROM people) AS pc
  FROM people
GROUP BY name
ORDER BY totalScore DESC

[Canman2005]

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

[Canman2005]

Still majorly stuck, is there a way to do this or am I trying to achive the impossible

[Mark Baker]

[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>';
}

 

[Canman2005]

I get

 

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[Mark Baker]

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>';
}

[Canman2005]

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?