Correlated Subquery Optimization 1

[subordin8]

I have been searching for the best way to do what I think should be a simple task in SQL. I have a table full of call history events and I want to get a summary of some events for each calling party that matches the form "username(extension)". Using correlated subqueries I do the following.

SELECT calling_party,

(SELECT SUM(end_time-start_time) AS total_time FROM `event`  ei WHERE event_type=4 AND ei.calling_party = eo.calling_party) AS total_talking_time, 

(SELECT SUM(end_time-start_time) AS total_time FROM `event` ei WHERE event_type=7 AND ei.calling_party = eo.calling_party) AS total_ringing_time,

(SELECT MAX(end_time-start_time) AS total_time FROM `event` ei WHERE event_type=4 AND ei.calling_party = eo.calling_party) AS max_talking_time

FROM `event` eo WHERE calling_party LIKE '%(%)'

GROUP BY calling_party

This works, but ends up taking a really long time to run. I figured that each subquery was getting executed multiple times. I made a quick php script that returns the same information but uses multiple queries.

$queryG = "SELECT DISTINCT calling_party FROM `event` WHERE calling_party LIKE '%(%)'";
$resultG = mysql_query($queryG) or die(mysql_error());

while($rowG = mysql_fetch_object($resultG)){
	$queryA = "SELECT 

	(SELECT SUM(end_time- start_time) FROM `event` WHERE event_type=4 AND calling_party LIKE '$rowG->calling_party') AS total_talking_time,  

	(SELECT SUM(end_time- start_time) FROM `event` WHERE event_type=7 AND calling_party LIKE '$rowG->calling_party') AS total_ringing_time, 

	(SELECT MAX(end_time- start_time) FROM `event` WHERE event_type=4 AND calling_party LIKE '$rowG->calling_party') AS max_talking_time";

	$resultA = mysql_query($queryA) or die(mysql_error());
	if($rowA = mysql_fetch_object($resultA)){
		//put data into a table or something
	}
}

So the question is what is the correct way to do this using SQL?

Cheers

 

[r937]

SELECT calling_party
     , SUM(CASE WHEN event_type = 4
                THEN end_time - start_time
                ELSE 0 END) AS total_talking_time
     , SUM(CASE WHEN event_type = 7
                THEN end_time - start_time
                ELSE 0 END) AS total_ringing_time
     , MAX(CASE WHEN event_type = 4
                THEN end_time - start_time
                ELSE 0 END) AS max_talking_time
  FROM `event` 
 WHERE calling_party LIKE '%(%)'
GROUP 
    BY calling_party

r937.com | rudy.ca

 

[subordin8]

That's it! Thanks a ton, that works perfect.