SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Sum in the rows

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Sum in the rows

    Hi all, I need your help.

    This is my table mysql:
    Code:
    ID	CO_PPM	H2_PPM	CH4_PPM	C2H6_PPM	C2H4_PPM	C2H2_PPM	TGCD	Old_State	New_State
    7257	26.00	14.00	1.00	1.00		1.00		0.00			SA	
    7258	33.00	29.00	1.00	1.00		1.00		0.00			SA	
    7259	38.00	6.00	1.00	1.00		1.00		0.00			SA	
    7260	143.00	1.00	1.00	2.00		1.00		0.00			SA	
    7261	92.00	6.00	1.00	1.00		1.00		0.00			SA	
    7262	76.00	25.00	1.00	1.00		1.00		0.00			SA	
    7263	357.00	7.00	1.00	2.00		1.00		0.00			SA	
    7264	78.00	1.00	1.00	1.00		1.00		0.00			SA	
    7265	137.00	27.00	1.00	1.00		1.00		0.00			AL1	
    7309	77.00	29.00	1.00	12.00		2.00		0.00			AL1
    I need this output:

    1. Sum of `CO_PPM+H2_PPM+CH4_PPM+C2H6_PPM+C2H4_PPM+C2H2_PPM` for single row (TGCD);
    2. Update the column `TGCD` with Sum value of point #1 (TGCD) ;
    3. Update the column `New_State` where this conditions:

    a) If TGCD < 1500 then SA ;
    b) If TGCD 1500<TGCD<3000 then AL1 ;
    c) If TGCD > 3000 then AL2.
    My difficulty is I've always done the sums of columns, not rows...

    Can you help me?
    Thanks in advance.

    Code:
    /*
    Navicat MySQL Data Transfer
    
    Source Server         : _MySQL
    Source Server Version : 50045
    Source Host           : localhost:3306
    Source Database       : db
    
    Target Server Type    : MYSQL
    Target Server Version : 50045
    File Encoding         : 65001
    
    Date: 2011-07-20 12:01:22
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `tbl_qa`
    -- ----------------------------
    DROP TABLE IF EXISTS `tbl_qa`;
    CREATE TABLE `tbl_qa` (
      `ID` int(10) NOT NULL auto_increment,
      `CO_PPM` decimal(10,2) default NULL,
      `H2_PPM` decimal(10,2) default NULL,
      `CH4_PPM` decimal(10,2) default NULL,
      `C2H6_PPM` decimal(10,2) default NULL,
      `C2H4_PPM` decimal(10,2) default NULL,
      `C2H2_PPM` decimal(10,2) default NULL,
      `TGCD` decimal(10,2) default NULL,
      `Old_State` varchar(255) default NULL,
      `New_State` varchar(255) default NULL,
      PRIMARY KEY  (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7310 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of tbl_qa
    -- ----------------------------
    INSERT INTO tbl_qa VALUES ('7257', '26.00', '14.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7258', '33.00', '29.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7259', '38.00', '6.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7260', '143.00', '1.00', '1.00', '2.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7261', '92.00', '6.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7262', '76.00', '25.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7263', '357.00', '7.00', '1.00', '2.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7264', '78.00', '1.00', '1.00', '1.00', '1.00', '0.00', null, 'SA', '');
    INSERT INTO tbl_qa VALUES ('7265', '137.00', '27.00', '1.00', '1.00', '1.00', '0.00', null, 'AL1', '');
    INSERT INTO tbl_qa VALUES ('7309', '77.00', '29.00', '1.00', '12.00', '2.00', '0.00', null, 'AL1', '');

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    UPDATE tbl_qa
    SET 
        TGCD = CO_PPM+H2_PPM+CH4_PPM+C2H6_PPM+C2H4_PPM+C2H2_PPM
      , New_State = 
          CASE 
            WHEN CO_PPM+H2_PPM+CH4_PPM+C2H6_PPM+C2H4_PPM+C2H2_PPM < 1500 THEN 'SA'
            WHEN CO_PPM+H2_PPM+CH4_PPM+C2H6_PPM+C2H4_PPM+C2H2_PPM > 3000 THEN 'AL2'
            ELSE 'AL1'
          END

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Guido: many thanks for your help; your query working.

    But now I think other needs.

    In the `tbl_qa` I add two new fields: CODE_A and DATE_A, because you can have multiple rows for the same CODE_A and I need see only row with recent date.

    I try this SQL Select and I have the correct output:
    Code:
    SELECT t.*
    FROM ( 
    SELECT CODE_A
    , MAX(DATE_A) AS max_date
    FROM `tbl_qa`
    GROUP
    BY CODE_A ) AS m
    INNER JOIN `tbl_qa` AS t
    ON t.CODE_A = m.CODE_A
    AND t.DATE_A = m.max_date;
    The question is:
    With your query how can update the field `TGCD` and `New_State` in the table `tbl_qa` only for rows last (recent) date?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Try this
    Code:
    UPDATE tbl_qa AS t
    INNER JOIN
      ( 
        SELECT 
            CODE_A
          , MAX(DATE_A) AS max_date
        FROM `tbl_qa`
        GROUP BY CODE_A 
      ) AS m
    ON  t.CODE_A = m.CODE_A
    AND t.DATE_A = m.max_date
    SET 
        t.TGCD = t.CO_PPM+t.H2_PPM+t.CH4_PPM+t.C2H6_PPM+t.C2H4_PPM+t.C2H2_PPM
      , t.New_State = 
          CASE 
            WHEN t.CO_PPM+t.H2_PPM+t.CH4_PPM+t.C2H6_PPM+t.C2H4_PPM+t.C2H2_PPM < 1500 THEN 'SA'
            WHEN t.CO_PPM+t.H2_PPM+t.CH4_PPM+t.C2H6_PPM+t.C2H4_PPM+t.C2H2_PPM > 3000 THEN 'AL2'
            ELSE 'AL1'
          END

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Guido: wonderful solution!
    Great, thanks.


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
  •