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:
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:
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. but how do i get it to work correctly?!?
All help appreciated.
thanks.
jamied66
edited for code format error
chazzy
03-07-2008, 03:11 PM
to be honest, i'm not really sure what your expected output is. Let's say you have 3 rows, one for each day M, T, W. Each has the column acd_calls, with value 20. When you sum the 3 of these up, you'll get 60.
Do you want to group by date?
jamied66
03-07-2008, 03:15 PM
The output i'm looking for is for it to be the data for all of those days, summed or averaged, and grouped by tech.
for example, the 1 day query gives the following output:
I just want it to give a 3 day total for each tech, instead of one.
Am I way off base?
chazzy
03-07-2008, 06:01 PM
honestly, i think part of it, at least on my end, is some confusion over the functions you're using. for example, in your where clause you're using STR_TO_DATE, which should be used to take a string and turn it into a date, but then you're comparing them to strings (mysql doesn't implicitly convert them to date types in 4.1)
Then you have a whole bunch of
sec_to_time(sum(time_to_sec(str_to_date(....))))
and I just wonder if maybe there's an easier way to convert all of this, and maybe it's not converting properly at all?
There's really no reason why there would be a correlation between the # of days and output, unless of course the values are supposed to be tripled? i would think that if I do 20 things on Tuesday, 20 on Wednesday and 20 on Thursday, my total for the 3 days would be 60. but maybe i'm just thinking wrong?
jamied66
03-09-2008, 06:29 PM
no. that part of it works fine. I'm converting the date fields because the system that inputs them has to input them as a string. that part is no big deal.
What I want is a report that is grouped by each tech's name, and gives the totals for that range of dates.
for example: one tech's totals for 3 days individually and then the total using a simple sum function.
mysql> select tech_id, sum(total_calls) from data_agg where tech_id = '77701' and date = '2008-03-04' group by tech_id;
+---------+------------------+
| tech_id | sum(total_calls) |
+---------+------------------+
| 77701 | 76 |
+---------+------------------+
mysql> select tech_id, sum(total_calls) from data_agg where tech_id = '77701' and date = '2008-03-05' group by tech_id;
+---------+------------------+
| tech_id | sum(total_calls) |
+---------+------------------+
| 77701 | 76 |
+---------+------------------+
mysql> select tech_id, sum(total_calls) from data_agg where tech_id = '77701' and date = '2008-03-06' group by tech_id;
+---------+------------------+
| tech_id | sum(total_calls) |
+---------+------------------+
| 77701 | 48 |
+---------+------------------+
mysql> select tech_id, sum(total_calls) from data_agg where tech_id = '77701' and date between '2008-03-04' and '2008-03-06' group by tech_id;
+---------+------------------+
| tech_id | sum(total_calls) |
+---------+------------------+
| 77701 | 200 |
+---------+------------------+
The other table involved also provides the same accurate data, for brevity's sake:
mysql> select sum(acd_calls) from avaya_data where login_id = '77701' and str_to_date(date, '%m/%d/%Y') between '2008-03-04' and '2008-03-06';
+----------------+
| sum(acd_calls) |
+----------------+
| 140 |
+----------------+
Now here is where my confusion starts:
If I join the two in a simple query, the totals are multiplied out by the total number of days I pick, and I don't understand why.
SELECT
sum(avaya_data.acd_calls) as Avaya_Calls,
sum(data_agg.total_calls) as Zlinky_Calls,
data_agg.tech_id
FROM avaya_data
JOIN data_agg
ON avaya_data.login_id = data_agg.tech_id
WHERE date(data_agg.date) between '2008-03-04' and '2008-03-06'
and str_to_date(avaya_data.date, '%m/%d/%Y') between '2008-03-04' and '2008-03-06'
and data_agg.tech_id='77701'
and avaya_data.login_id = '77701'
GROUP BY data_agg.tech_id;
I know the correct values are 140 and 200, but they're multiplied out by 3 (the number of days i'm asking for). If i ask for 4 days of data, it multiplies out by 4. I know i'm doing something wrong, but what???
Any help is appreciated.
Thanks.
-jamie
chazzy
03-09-2008, 09:35 PM
ok.
i think part of what i'm missing is how your data model is setup, but are you sure that you're not ending up w/ a cross product in there due to how you're joining them? it looks like you're comparing dates on both tables, is it necessary? that's the only thing i can think of that would be causing this to happen.
jamied66
03-10-2008, 06:36 AM
each table keeps a running 8 days worth of data in it, so if I'm only looking for 3 days worth, I'd have to compare dates, right? (not being a smart-aleck, just learning this on the fly).
This is how the 2 tables are set up:
mysql> explain data_agg;
+-------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+----------------+
| tech_id | int(7) | YES | | NULL | |
| total_calls | int(3) | YES | | NULL | |
| date | date | YES | | NULL | |
| pri | int(6) | NO | PRI | NULL | auto_increment |
+-------------+--------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
This has begun to drive me nuts. I'm not asking it to do anything miraculous. I think the problem is in how I'm joining them somehow, but I've tried every way I can think of with no improvement, and 2 other projects are hinging on it.
How would you (and by you I mean anyone willing to toss an idea out) join these two tables to pull a sum from the two columns I'm talking about properly, cause i'm stumped?
jamied66
03-10-2008, 10:43 AM
mysql> EXPLAIN EXTENDED
-> 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; SHOW WARNINGS;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | members | ALL | NULL | NULL | NULL | NULL | 239 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | data_agg | ALL | NULL | NULL | NULL | NULL | 1332 | Using where |
| 1 | SIMPLE | avaya_data | ALL | NULL | NULL | NULL | NULL | 2120 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `zlinky2`.`members`.`username` AS `username`,`zlinky2`.`members`.`agent_id` AS `agent_id`,sum(`zlinky2`.`avaya_data`.`acd_calls`) AS `Avaya_Calls`,sec_to_time(avg(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`avg_acd_time`,_utf8'%H :%i')))) AS `AHT`,sum(`zlinky2`.`avaya_data`.`extn_out_calls`) AS `Out_Calls`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`aux_time`,_utf8'%H:%i')) )) AS `Aux_Time`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`wrap_up`,_utf8'%H:%i')))) AS `Wrap_up`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`break`,_utf8'%H:%i')))) AS `Break`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`training`,_utf8'%H:%i')))) AS `Training`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`clerical`,_utf8'%H:%i'))) ) AS `Clerical`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`repair`,_utf8'%H:%i')))) AS `Repair`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`lunch`,_utf8'%H:%i')))) AS `Lunch`,sec_to_time(sum(time_to_sec(str_to_date(`zlinky2`.`avaya_data`.`psw`,_utf8'%H:%i')))) AS `PSW`,sum(`zlinky2`.`avaya_data`.`trans_out`) AS `Xfers`,sum(`zlinky2`.`avaya_data`.`rolled_calls`) AS `Rolled_calls`,sum(`zlinky2`.`data_agg`.`total_calls`) AS `Zlinky_Calls` from `zlinky2`.`avaya_data` join `zlinky2`.`members` join `zlinky2`.`data_agg` where ((`zlinky2`.`data_agg`.`tech_id` = `zlinky2`.`members`.`agent_id`) and (`zlinky2`.`avaya_data`.`login_id` = `zlinky2`.`members`.`agent_id`) and (str_to_date(`zlinky2`.`avaya_data`.`date`,_utf8'%m/%d/%Y') between _utf8'2008-03-04' and _utf8'2008-03-06') and (cast(`zlinky2`.`data_agg`.`date` as date) between _utf8'2008-03-04' and _utf8'2008-03-06') and (`zlinky2`.`members`.`locator` like _utf8'R%R')) group by `zlinky2`.`members`.`username` order by `zlinky2`.`members`.`username` |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
chazzy
03-10-2008, 11:28 AM
so then a single row for each table will contain an entire day's worth of information for the tech?
Assuming that statement is correct:
i think you're going to need to use a subselect. I see how it's ending up like this now (sorry for taking so long, i've been mostly talking myself through this). First thing you want to do is pair up one day from each table in a view (or just subselect). then do your summation based on that.
What's happening right now, is let's take these days:
3/8/2008
3/9/2008
3/10/2008
When you do the select the way you're doing it, you're ending up with 9 pairs (we only want 3)
3/8 -> 3/8
3/8 -> 3/9
3/8 -> 3/10
3/9 -> 3/8
3/9 -> 3/9
3/9 - > 3/10
3/10 -> 3/8
3/10 -> 3/9
3/10 -> 3/10
We only want #1,#5, and #9 out of this list.
maybe you could even just add in a t1.date = t2.date and that'll fix it.
jamied66
03-10-2008, 11:52 AM
OK.
I TOTALLY get the logic of what you're saying, and I REALLY want to get this figured out. I've got my O'Reilly book out and am reading up, but can you possibly point me in how to begin to structure my query this way?
THANKS AGAIN.
-jamie
jamied66
03-10-2008, 12:26 PM
it's still early, BUT
SELECT
sum(t1.acd_calls) as Avaya_Calls,
sum(t2.total_calls) as Zlinky_Calls,
t2.tech_id
FROM avaya_data as t1
JOIN data_agg as t2
ON t1.login_id = t2.tech_id
WHERE date(t2.date) between '2008-03-04' and '2008-03-06'
and str_to_date(t1.date, '%m/%d/%Y') = t2.date
and t2.tech_id='77701'
GROUP BY t2.tech_id;
seems to be giving sane data.
WOW. thanks for the help!
chazzy
03-10-2008, 12:53 PM
Yep, that's pretty much what I was pointing to. Good job on figuring it out!
the inclusion of more columns shouldn't harm the results, so you should be good.
webdeveloper.com
Copyright Internet.com Inc., All Rights Reserved.