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