MySQL - Operations with conditions

Question

I'm trying to perform a SUM() operation with three similar if() conditions, with the following format:

First one:

if((TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > high OR TIME IS NULL) AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i')

Second one:

if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > value AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i')

Third one:

if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) >0 AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i')

Being high and value two different fields columns from a couple of tables.

Unfortunately, I can't seem to make this work, most probably due to that this sentences can't be SUM()'d together.

How can this be done?

Edit: Added entire query:

SELECT VALUE1 ,
 COUNT( * ) TOTAL 
 from ( 
  SELECT (
   if((TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > high OR TIME IS NULL) AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i')) + 
   if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > value AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i')) + 
   if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) >0 AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i'))) as 'ok',
if((TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > high OR TIME IS NULL) AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i'),4, 
if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) > value AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i'),3, 
if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) >0 AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') < DATE_FORMAT(now(), '%Y-%m-%d %H:%i'),2, 
if(TIME >'0000-00-00 00:00:00', 1,0) ))) VALUE1 
FROM table1 , table2 left outer join table3 on( value2 = value3 ) where id_param='col1' and col2 = DATE_FORMAT(if(CURTIME()>='00:00:00' and CURTIME() < '08:00:00',sysdate() - INTERVAL 1 DAY, sysdate()), '%Y-%m-%d') ) as datos GROUP BY VALUE1

This should return a 2columnx3rows table, with values for 'ok', '4', '3' and '2'.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

You could do

SELECT (if_cond1 + if_cond2 + if_cond3) as `x` FROM...

(expanding the if_cond1,2,3 as per your IFs above).

And if you want to simplify how that SQL might look once expanded (very big), you can use sub-queries like this.

edited May 23 '17 at 12:27

Community

1
1

answered Sep 11 '12 at 08:23

aneroid

12,983
3
36
66

Unfortunately, it doesn't seems to work. Maybe I'm doing something wrong. Adding entire query to main post in a sec. – AleksanderKseniya Sep 11 '12 at 08:45
Can't really help there without the db structure + sample data. Anyway, try incrementally, do add one `if_cond`s at a time and see where things are not adding up. (assuming there are no syntax errors). – aneroid Sep 11 '12 at 09:31

MySQL - Operations with conditions

1 Answers1