Results 1 to 2 of 2

Thread: Duplicating infomation with mysql group ...

  1. #1
    Join Date
    Nov 2008
    Posts
    52
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Duplicating infomation with mysql group ...

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Dublin, Ireland
    Posts
    23
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

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

    Code:
    // 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'];

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •