Duplicating infomation with mysql group ...

[pepe_lepew1962]

Hello:

I am trying to get MAX, MIN and AVG totals along with the basic information for groups of SupplierIDs with mysql. I know there will be duplicate information and that is the intent. Any help would greatly be appreciated.


CREATE TABLE tblSupplier
(
tblSupplier_ID CHAR(3) NOT NULL,
tblSupplier_Name CHAR(4) NOT NULL,
tblSupplier_Model VARCHAR(7)
)
;
//
CREATE TABLE tblPart
(
tblPart_ID CHAR(3) NOT NULL,
tblPart_Type CHAR(8) NOT NULL,
tblPart_Price DECIMAL(6,2)
)
;
//
//
INSERT INTO tblSupplier (tblSupplier_ID,tblSupplier_Name,tblSupplier_Model) VALUES ('125','Sony','Desktop');
INSERT INTO tblSupplier (tblSupplier_ID,tblSupplier_Name,tblSupplier_Model) VALUES ('136','Sony','Desktop');
//
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','DRIVE','500.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','VIDEO','100.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','KEYBOARD','63.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('136','DRIVE','450.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('136','VIDEO','300.00');
//
//
//
Result Fields:
tblSupplier_Name,tblSupplier_ID,tblPart_Type, MAX(tblPart_Price), MIN(tblPart_Price), AVG(tblPart_Price)
Sony-125-DRIVE-500.00-500.00-63.00-221.00
Sony-125-VIDEO-100.00-500.00-63.00-221.00
Sony-125-KEYBOARD-63.00-500.00-63.00-221.00

Sony-136-DRIVE-450.00-450.00-300.00-375.00
Sony-136-VIDEO-300.00-450.00-300.00-375.00

[Butterfly]

Here's an example from one of my databases. It might set you on the right track.


// Get min and max values from the database
$result = mysql_query("SELECT MIN(weight) AS weight FROM diamonds");
$row = mysql_fetch_array($result);
$wmin = $row['weight'];

$result = mysql_query("SELECT MAX(weight) AS weight FROM diamonds");
$row = mysql_fetch_array($result);
$wmax = $row['weight'];