Table select options

[austinderrick2]

I want to select the biggest weight(weight in table) of male(gender in table) animals for each type of animal(type in table) and add them together to get the biggest combined weight of the top weights for each type from each person.

Here is my table.....

# phpMyAdmin MySQL-Dump
# version 2.2.1-dev
# <a href="http://phpwizard.net/phpMyAdmin/" target="_blank">[url]http://phpwizard.net/phpMyAdmin/[/url]</a>
# <a href="http://phpmyadmin.sourceforge.net/" target="_blank">[url]http://phpmyadmin.sourceforge.net/[/url]</a> (download page)
#
# Host: members-free-php01.uk.db.lyceu.net:3309
# Generation Time: Feb 20, 2003 at 10:41 PM
# Server version: 3.23.33
# PHP Version: 4.0.6
# Database : `lakesidecabin8_uk_db`
# --------------------------------------------------------

#
# Table structure for table `trophys`
#

CREATE TABLE trophys (
  oid int(11) NOT NULL auto_increment,
  name text NOT NULL,
  email text NOT NULL,
  gender text NOT NULL,
  weight int(11) NOT NULL default '0',
  location text NOT NULL,
  weapon text NOT NULL,
  scope text NOT NULL,
  distance text NOT NULL,
  credits text NOT NULL,
  bonus text NOT NULL,
  worldrecord text NOT NULL,
  time text NOT NULL,
  date text NOT NULL,
  type text NOT NULL,
  PRIMARY KEY  (oid),
  UNIQUE KEY oid (oid)
) TYPE=MyISAM;

#
# Dumping data for table `trophys`
#

INSERT INTO trophys VALUES (1,'Derrick','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','elk');
INSERT INTO trophys VALUES (2,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','caribou');
INSERT INTO trophys VALUES (3,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','moose');
INSERT INTO trophys VALUES (4,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','blackbear');
INSERT INTO trophys VALUES (5,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','brownbear');
INSERT INTO trophys VALUES (6,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','bighornsheep');
INSERT INTO trophys VALUES (7,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','mountaingoat');
INSERT INTO trophys VALUES (8,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','mountainlion');
INSERT INTO trophys VALUES (9,'Derrick Austin','austinderrick2@hotmail. com','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','wolf');
INSERT INTO trophys VALUES (10,'Derrick Austin','austinderrick2@hotmail. com','Female',150,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','wolf');
INSERT INTO trophys VALUES (11,'Derrick','austinderrick2@hotmail. com','Male',120,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','elk');

[sfullman]

In mysql this is a two or three-part operation.

You're seeking to get, for each individual, the combined max weights of males from all available species.

Run this query:
select MAX(AnimalWeight) AS biggestMale
from yourTable
where gender = Male and
Hunter = 'Bob'
group by Hunter, AnimalType

Then you'll have to run through the results, and sum up the biggestMale field to arrive at the total weight.