When Current column and Previous column > 0, then do this-CodePudding

mysql> select * from table;
 ------ ------ ------ ------- 
| id   | cnta | cntb |  cntc | 
 ------ ------------- ------- 
4         0      1      2   
3         2      3      0
2         1      0      1   
1         3      2      2

I would like to compare two sequential rows (current column and previous column) and if they are both greater than 0, I'd like to sum the results of the sequential rows.

this is what I tried and failed:

SELECT
         g1.id,
(case        
    When g2.cnta > 0 and g1.cnta > 0 then g1.cnta ELSE 0) End as cnta   
(case        
    When g2.cntb > 0 and g1.cntb > 0 then g1.cntb ELSE 0) End as cntb       
(case        
    When g2.cntc > 0 and g1.cntc > 0 then g1.cntc ELSE 0) End as cntc 
 
FROM table g1 INNER JOIN table g2 ON g2.id = g1.id  1;

the final output I'm trying to get is like this (if current column and previous column > 0, then current column1 etc ) :

id    totalcnt
4        1   
3        2
2        2
1

How can I fix my query? or can I get alternative approach as a solution, please?

** I forgot to mention that there are no NULL values in my table. Only 0s and positive integers.

CodePudding user response：

If your last row of the result is not to be empty, try this:

SELECT  t1.id, (CASE  WHEN t1.cnta>0 AND t2.cnta>0 THEN t1.cnta ELSE 0
END     CASE   WHEN t1.cntb>0 AND t2.cntb>0 THEN t1.cntb ELSE 0 END 
CASE  WHEN t1.cntc>0 AND t2.cntc>0 THEN t1.cntc ELSE 0 END  ) AS
cValue    FROM table1 t1 LEFT JOIN table1 t2 ON t2.id=t1.id-1;

OR if you really want it to be empty, you can use a subquery

SELECT t1.id,IFNULL((SELECT (CASE  WHEN t1.cnta>0 AND t2.cnta>0 THEN
t1.cnta ELSE 0 END     CASE   WHEN t1.cntb>0 AND t2.cntb>0 THEN
t1.cntb ELSE 0 END  CASE  WHEN t1.cntc>0 AND t2.cntc>0 THEN t1.cntc
ELSE 0 END  ) FROM table1 t2 WHERE t2.id=t1.id-1),'') cValue FROM
table1 t1

CodePudding user response：

SELECT t1.id,
       (t1.cnta * t2.cnta > 0) * t1.cnta
       (t1.cntb * t2.cntb > 0) * t1.cntb
       (t1.cntc * t2.cntc > 0) * t1.cntc totalcnt
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.id   1;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=48f296035e95bf4c7331427e82c25619

t1.cntX * t2.cntX is NULL if at least one value is NULL, is zero if at least one value is zero, and is 1 if both values are not zero/NULL.