group by giving odd results.
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
