Databases
StackOverflow2013
Postsdbo
POMySQL - Operations with conditions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - Operations with conditions
    primarykey
    data
    text
    <p>I'm trying to perform a <code>SUM()</code> operation with three similar <code>if()</code> conditions, with the following format:</p> <p>First one:</p> <p><code>if((TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt; high OR TIME IS NULL) AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i')</code></p> <p>Second one:</p> <p><code>if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt; value AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i')</code></p> <p>Third one:</p> <p><code>if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt;0 AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i')</code></p> <p>Being <code>high</code> and <code>value</code> two different fields columns from a couple of tables.</p> <p>Unfortunately, I can't seem to make this work, most probably due to that this sentences can't be <code>SUM()</code>'d together. </p> <p>How can this be done?</p> <p>Edit: Added entire query:</p> <pre><code>SELECT VALUE1 , COUNT( * ) TOTAL from ( SELECT ( if((TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt; high OR TIME IS NULL) AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i')) + if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt; value AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i')) + if(TIME_TO_SEC(TIMEDIFF(IF(TIME = '0000-00-00 00:00:00',sysdate() , TIME ), TIME2)) &gt;0 AND TIME3 != '0000-00-00 00:00:00' AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; 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)) &gt; high OR TIME IS NULL) AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; 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)) &gt; value AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; 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)) &gt;0 AND DATE_FORMAT(TIME2, '%Y-%m-%d %H:%i') &lt; DATE_FORMAT(now(), '%Y-%m-%d %H:%i'),2, if(TIME &gt;'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()&gt;='00:00:00' and CURTIME() &lt; '08:00:00',sysdate() - INTERVAL 1 DAY, sysdate()), '%Y-%m-%d') ) as datos GROUP BY VALUE1 </code></pre> <p>This should return a 2columnx3rows table, with values for 'ok', '4', '3' and '2'.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAleksanderKseniya
    1. USAleksanderKseniya
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - Operations with conditions
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload