Sum in the rows

Databases
1

Hi all, I need your help.

This is my table mysql:


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:

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

Can you help me?
Thanks in advance.

/*
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 

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

Guido: many thanks for your help; your query working. :slight_smile:

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:

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

Try this


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 &lt; 1500 THEN 'SA'
        WHEN t.CO_PPM+t.H2_PPM+t.CH4_PPM+t.C2H6_PPM+t.C2H4_PPM+t.C2H2_PPM &gt; 3000 THEN 'AL2'
        ELSE 'AL1'
      END
5

Guido: wonderful solution!
Great, thanks.