Find the largest value for each group and sum

[Skipjackrick]

I can't seem to wrap my brain around how to do this.  I've tried using MAX() and tried writing it out long hand....I just can't seem to get this.

 

Here is an example of what I am trying to do.  This is a fishing website so .... you get the drift.

 

Example, Team XYZ has two anglers

 

Angler A catches the following:

Bass = 16 inches

Bass = 18 inches

Catfish = 19 inches

Catfish = 17 inches

 

Angler B catches the following:

Bass = 17.5 inches

Bass = 18.3 inches

Catfish = 20 inches

Catfish = 21 inches

 

 

I want to return the Sum of the inches of only the LARGEST catch for each species for both anglers..

 

The result should be...

Angler A = 37 inches

Angler B = 39.3 inches

 

TEAM XYZ = 76.3 inches

 

Any thoughts?

 

 

[Muddy_Funster]

using this:

SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1

you can get the first part of it (and angler A is 36 not 37).  using this as a foundation, and applying it to the other fields in your table, you sould get the second part ok on your own.  Any further problems post up your full table structure

[Skipjackrick]

using this:

SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1

you can get the first part of it (and angler A is 36 not 37).  using this as a foundation, and applying it to the other fields in your table, you sould get the second part ok on your own.  Any further problems post up your full table structure

 

Thanks,

 

I'll give this a go and report back with the results.

[mikosiko]

using this:

SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1

you can get the first part of it (and angler A is 36 not 37). ....

 

37 is correct... seems that you didn't understand the OP's requirements

I want to return the Sum of the inches of only the LARGEST catch for each species for both anglers

Angler A largest catch for each species is:

Bass = 18 inches

Catfish = 19 inches

 

which is equal to 37.

 

your query doesn't do the job..

 

@Skipjackrich :

here is something for you to explore:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

 

[Skipjackrick]

using this:

SELECT SUM(size), name FROM s1 GROUP BY name ORDER BY size DESC LIMIT 1

you can get the first part of it (and angler A is 36 not 37).  using this as a foundation, and applying it to the other fields in your table, you sould get the second part ok on your own.  Any further problems post up your full table structure

 

Got an empty result..

 

 

<?php
      //Testing for total inches
      $total_inches = "SELECT
                SUM(length), angler
                FROM submit
                GROUP BY length
                ORDER BY length DESC LIMIT 1
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Total Inches</th></tr>";
$tablefoot = "</table>";

$rank=1;
while($row = mysql_fetch_assoc($total_inches_result)) {

$team = $row['team_id'];
$total = $row['SUM(length)'];
   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $total
              </td>
              <td>
                 $min_length
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $anglerdetails
       $tablefoot
EOD;

print $table;

?>

[Skipjackrick]

 

 

@Skipjackrich :

here is something for you to explore:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

 

 

Thanks let me read that....just a brief look over it looks like what I need.

[Skipjackrick]

In case you were interested....here is my table structure

 

Fields

+-------+------------+------------+----------------+-----------+-----------+

    id      team_id    angler      species_id      points      length

+-------+------------+------------+----------------+-----------+-----------+

[Skipjackrick]

Ok....

 

I am just sort of working through the tutorial you sent me to..  I am getting results but its not displaying my results.  Do you see anything weird?

 

<?php
//connect to the db
include 'db_connect.php';


      //Testing for total inches
      $total_inches = "SELECT
                team_id, angler, species_id, length 
                FROM submit
                WHERE length = (SELECT MAX(length) from submit as total where total.angler = submit.species_id)
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Angler</th> <th>Species</th> <th>Length</th></tr>";
$tablefoot = "</table>";

echo "There are $RowCount rows in your results.<br />";

$rank=1;
while($row = mysql_fetch_array($total_inches_result)) {

$team = $row['team_id'];
$angler = $row['angler'];
$species = $row['species'];
$length = $row['length'];

   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $angler
              </td>
              <td>
                 $species
              </td>
              <td>
                 $length
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $anglerdetails
       $tablefoot
EOD;

print $table;

?>

[Muddy_Funster]

could be wrong here, but I think your problem is:

while($row = mysql_fetch_array($total_inches_result)) {

mysql_fetch_assoc() would be the preffered call as I think that mysql_fetch_array stores each record in a set under it's field number (starting at zero) in the order that it selected by - also, I think it's been depriciated, not sure though.

you can still use mysql_fetch_array() just change it to $row[0], $row[1] etc.

 

P.S. : sorry for posting the wrong code, mikosiko was right, I missread your desired output.

[mikosiko]

I didn't read all your code... your query is wrong....  here is a small example (one way to solve your original questions) that you can use/adjust according to your needs.

SELECT angler, SUM(mlenght)
FROM (
        SELECT angler, MAX(lenght) AS mlenght
           FROM anglers GROUP BY angler, specie
     ) AS zz
group by angler;

[mikosiko]

could be wrong here, but I think your problem is:

while($row = mysql_fetch_array($total_inches_result)) {

mysql_fetch_assoc() would be the preffered call as I think that mysql_fetch_array stores each record in a set under it's field number (starting at zero) in the order that it selected by - also, I think it's been depriciated, not sure though.

you can still use mysql_fetch_array() just change it to $row[0], $row[1] etc.

 

P.S. : sorry for posting the wrong code, mikosiko was right, I missread your desired output.

..

 

sorry to say this.... but you are wrong again ... for you to read:

http://php.net/manual/en/function.mysql-fetch-array.php

 

[Skipjackrick]

I didn't read all your code... your query is wrong....  here is a small example (one way to solve your original questions) that you can use/adjust according to your needs.

SELECT angler, SUM(mlenght)
FROM (
        SELECT angler, MAX(lenght) AS mlenght
           FROM anglers GROUP BY angler, specie
     ) AS zz
group by angler;

 

This actually got results...but how do I display them using php?

 

It returned 342 rows..

 

<?php
//connect to the db
include 'db_connect.php';


      //Testing for total inches
      $total_inches = "SELECT
                angler, SUM(mlength) 
                FROM (
                         SELECT angler, MAX(length) AS mlength
                            FROM submit GROUP BY angler, species_id
                     ) AS zz
      GROUP BY angler
                ";
      $total_inches_result = mysql_query($total_inches) or die(mysql_error());

      $RowCount = mysql_num_rows($total_inches_result);


$tablehead = "<table border='1'><tr> <th>Rank</th> <th>Team</th> <th>Angler</th> <th>Species</th> <th>Length</th></tr>";
$tablefoot = "</table>";

echo "There are $RowCount rows in your results.<br />";

$rank=1;
while($row = mysql_fetch_array($total_inches_result)) {

$team = $row['team_id'];
$angler = $row['angler'];
$species = $row['species'];
$total = $row['zz'];

   $tabledetails .=<<<EOD
           <tr>
              <td>
                 $rank
              </td>
              <td>
                 $team
              </td>
              <td>
                 $angler
              </td>
              <td>
                 $species
              </td>
              <td>
                 $total
              </td>
           <tr>
EOD;
$rank++;
} 

$table .=<<<EOD
       $tablehead
       $tabledetails
       $tablefoot
EOD;

print $table;

?>

[Skipjackrick]

AHA!!

 

I need to use SUM(mlength)

[Maq]

AHA!!

 

I need to use SUM(mlength)

 

Has this issue been resolved?

[mikosiko]

I said:

... that you can use/adjust according to your needs.

 

copy/paste alone is not going to give you immediate results.. is YOUR job to :

- Test the code given to you first (using Phpmyadmin or whatever you use to run queries), and analyze if it is giving the results that you want, or adjust it properly.

 

- Then you can adjust your code (php) to show the data produced by the correct query in the way that you want.

 

?

 

is some leg work to do in your side my friend.... as a very well know comic say... "C'mon  YOU can do it!!"

 

 

[Skipjackrick]

I didn't read all your code... your query is wrong....  here is a small example (one way to solve your original questions) that you can use/adjust according to your needs.

SELECT angler, SUM(mlenght)
FROM (
        SELECT angler, MAX(lenght) AS mlenght
           FROM anglers GROUP BY angler, specie
     ) AS zz
group by angler;

 

THANKS SO MUCH!!!!

 

This is exactly what I needed.....now I am just working through customizing it for me....

[Skipjackrick]

AHA!!

 

I need to use SUM(mlength)

 

Has this issue been resolved?

 

Yeah, I mostly got it figured out.....

 

I like your avatar....you must be a male?  LOL....

 

I work as a pharmaceutical scientist during the day.

[Muddy_Funster]

...

sorry to say this.... but you are wrong again ... for you to read:

http://php.net/manual/en/function.mysql-fetch-array.php

lol c'mon admit it, your not sorry at all 

I had read that, it's just been a while, and I have only used it with row numbers, having always used assoc - that said, I did clearly get mixed up with mysql_fetch_row() and once again posted poor information. :'(

[Maq]

AHA!!

 

I need to use SUM(mlength)

 

Has this issue been resolved?

 

Yeah, I mostly got it figured out.....

 

I like your avatar....you must be a male?  LOL....

 

I work as a pharmaceutical scientist during the day.

 

Very much so