IF statement in mySQL query

crusher88

Say I have a table like this:

Cusid	WorkTime	DebTime
-----	--------	-------
  1	   4			
  1			   1	
  1	   6		   4
  1	   3		
  1	   		   3
--------------------------------

Can someone translate this to SQL?

If DebTime is empty, then sum WorkTime.
If both DebTime and WorkTime are not empty, then sum DebTime, else sum DebTime
(DebTime has priority over WorkTime when DebTime is not null)

bubblenut

If I understand what you're asking for (which is pretty damned hard, could you take the time to figure out exactly what you mean and articulate it as such in future please?) you cannot. Not with one query anyway.

Here are two queries which achieve the sollution I think you're looking for.

SELECT SUM(WorkTime) FROM table WHERE DebTime IS NULL;

SELECT SUM(DebTime) FROM table;

crusher88

Sorry, I hit the submit button a little to early 🙂

This is my best try:

$query = "SELECT *, "
	. " CASE WHEN `DebTime` IS NULL THEN "
	. " SUM( WorkTime ) as Value"
	. " ELSE "
	. " SUM( DebTime ) as Value"
	. " END "
	. " FROM `table`"
	. " WHERE 1 " 
        . " GROUP BY `cusid` "
        . " ORDER BY `cusid` ";

The problem is that if the DebTime column is empty, I want to sum the WorkTime column. And if both columns have a value, I want to sum the DebTime column.

So the result should be displayed as this (I don't want to sum the WorkTime in rows where there is a value in DebTime):

cusid	Value
-----   -----
  1       15

bubblenut

Yep, and that's what my two queries do. Think back to "The Lion King" when Rafiki's beside the pool with Simba ... "Look deeper"

$sql="SELECT SUM(WorkTime) FROM table WHERE DebTime IS NULL";
$res=mysql_query($sql);
list($value)=mysql_fetch_row($res);

$sql="SELECT SUM(DebTime) FROM table";
$res=mysql_query($sql);
$row=mysql_fetch_row($res);
$value+=$row[0];

You can mess around all day trying to fit it into one query but a. you'll waste all day on it and b. it won't actually be very neat.

Lars_Berg

select cusid,sum(coalesce(debtime,worktime))
from t 
group by cusid