MySQL Sum Multiple Tables Question

[n3mesis125]

Hey Folks,

 

I'm sure people have asked this over and over, but I am at a lost and have tried to google this issue for a solution.

 

I have the following query:

 

SELECT t.date, t.username, t.site, COUNT(t.date) AS Tracked, 
u.avaya_id, SUM(p.calls) AS mycalls, SUM(p.outgoing_calls), AVG(p.aht_blend), SUM(p.calls_held), AVG(p.agent_occup), 
u.employee_num, SUM(p.aux), SUM(p.avail), m.name AS Mngr
FROM tracker_data as t
LEFT JOIN users as u
  ON (t.employee_num = u.employee_num)
LEFT JOIN ecp_data as p
  ON (p.avaya_id = u.avaya_id AND p.date = t.date)
LEFT JOIN management as m
  ON (u.manager = m.tag)
WHERE t.date".$date_range."
AND t.cat_type='phone'
GROUP BY t.username
ORDER BY t.username

 

There are 4 tables:

tracker_data - this table tells me how much ppl are tracking

ecp_data - this table tells me exactly how much work ppl get

users - this table holds all profile info

management - this table holds all management info

 

I'm trying to SUM() columns in ecp_data and COUNT() entries from the tracker_data table. But when there is a large range ie date BETWEEN '' AND '', then its not summing up the totals correctly. Can anyone figure out with my query above what I'm doing wrong?

 

Tks,

n3m.

[ninedoors]

I would also like to know this answer as I am trying to do something very similar.

[n3mesis125]

I actually figured it out on my own by browsing around

 

This is what I did to make it work, not sure if there is a more efficient way, but this got the job done.

 

Variable $date_range being the range the end-user picks from some menu drop-downs.

 

SELECT t.date, t.employee_num, t.username, t.site, COUNT(t.date) AS Tracked, 
u.avaya_id, Avaya.calls as calls, Avaya.ocalls AS outgoing_calls, Avaya.caht AS aht_blend, Avaya.cheld AS calls_held, Avaya.occup AS agent_occup,
u.employee_num, Avaya.aux AS aux, Avaya.avail as avail, m.name AS Mngr
FROM tracker_data as t
LEFT JOIN users as u
ON (t.employee_num = u.employee_num)
LEFT JOIN (
SELECT date, avaya_id, SUM(calls) AS calls, SUM(outgoing_calls) AS ocalls, AVG(aht_blend) AS caht, SUM(calls_held) AS cheld,
  AVG(agent_occup) AS occup, SUM(aux) AS aux, SUM(avail) AS avail
FROM ecp_data
WHERE date".$date_range."
GROUP BY avaya_id
) AS Avaya ON (Avaya.avaya_id = u.avaya_id)
LEFT JOIN management as m
ON (u.manager = m.tag)
WHERE t.date".$date_range."
AND t.cat_type='phone'
GROUP BY t.username
ORDER BY t.username

 

Hope this helps someone else out that is stuck. You basically just need to use sub-queries to have cross-table sums that add up properly. At least thats what I've gathered.

 

Tks,

n3m.

[ninedoors]

That is essentially what I did as well but was looking for a cleaner or faster solution.  Thanks for posting n3m

[n3mesis125]

Interesting enough, not sure why this wouldn't work, but in the sub-select query I did with LEFT JOIN the ecp_data table. I originally tried AS Avaya ON (Avaya.avaya_id = u.avaya_id AND t.date = Avaya.date), oddly enough, it wouldn't SUM the data correctly when I tried to match the table's date column with the tracker_data t.date column. So I had to add in a WHERE clause to match the $date_range variable.