I'm using:
mysql Ver 14.12 Distrib 5.0.26, for pc-linux-gnu (i686) using readline 5.1

within a PHP Version 5.2.2-pl1-gentoo script.

I have 3 tables:

members: usernames with unique id's
avaya_data: data from a phone system. one row per tech for each date containing all data. contains the same unique ID.
data_agg: data from an inhouse system and a total of calls taken for each tech for each date. also one row per tech for each date. also contains the same unique id.

If I pull data for 1 date, using a query like:

Code:
SELECT 
members.username,
members.agent_id,
sum(avaya_data.acd_calls) as Avaya_Calls,
sec_to_time(avg(time_to_sec(str_to_date(avaya_data.avg_acd_time, '%H:%i')))) as AHT,
sum(avaya_data.extn_out_calls) as Out_Calls,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.aux_time, '%H:%i')))) as Aux_Time,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.wrap_up, '%H:%i')))) as Wrap_up,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.break, '%H:%i')))) as Break,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.training, '%H:%i')))) as Training,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.clerical, '%H:%i')))) as Clerical,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.repair, '%H:%i')))) as Repair,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.lunch, '%H:%i')))) as Lunch,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.psw, '%H:%i')))) as PSW,
sum(avaya_data.trans_out) as Xfers,sum(avaya_data.rolled_calls) as Rolled_calls, 
sum(data_agg.total_calls) as Zlinky_Calls 
FROM avaya_data 
	JOIN members on members.agent_id = avaya_data.login_id 
	JOIN data_agg ON members.agent_id = data_agg.tech_id 
WHERE str_to_date(avaya_data.date, '%m/%d/%Y') = (CURDATE()- interval 1 day) 
	AND date(data_agg.date) = (CURDATE()- interval 1 day) 
	AND members.locator LIKE 'R%R' 
GROUP BY members.username 
ORDER BY members.username
(pulling only 1 day), it works perfectly.

If I run a query like this:

Code:
SELECT 
members.username,
members.agent_id,
sum(avaya_data.acd_calls) as Avaya_Calls,
sec_to_time(avg(time_to_sec(str_to_date(avaya_data.avg_acd_time, '%H:%i')))) as AHT,
sum(avaya_data.extn_out_calls) as Out_Calls,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.aux_time, '%H:%i')))) as Aux_Time,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.wrap_up, '%H:%i')))) as Wrap_up,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.break, '%H:%i')))) as Break,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.training, '%H:%i')))) as Training,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.clerical, '%H:%i')))) as Clerical,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.repair, '%H:%i')))) as Repair,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.lunch, '%H:%i')))) as Lunch,
sec_to_time(sum(time_to_sec(str_to_date(avaya_data.psw, '%H:%i')))) as PSW,
sum(avaya_data.trans_out) as Xfers,
sum(avaya_data.rolled_calls) as Rolled_calls, 
sum(data_agg.total_calls) as Zlinky_Calls 
FROM avaya_data 
	JOIN members on members.agent_id = avaya_data.login_id 
	JOIN data_agg ON members.agent_id = data_agg.tech_id 
WHERE str_to_date(avaya_data.date, '%m/%d/%Y') BETWEEN '2008-03-04' AND '2008-03-06' 
	AND date(data_agg.date) BETWEEN '2008-03-04' AND '2008-03-06' 
	AND members.locator LIKE 'R%R' 
GROUP BY members.username 
ORDER BY members.username
(a range of dates), then all of the sums are multiplied out by the range of dates i select. For example, if the acd_calls field = 20 for 3 days, it would show 60.

I understand what it's doing, and I'm pretty sure it has to do with how i'm grouping it, but how do i get it to work correctly?!?

All help appreciated.

thanks.

jamied66