I'm rather stumped here with an SQL statement between two tables, and a sum with group by clause.
Table 1: num_calls_data
numcallsid, date_added, date_of_call, teamlead, agent, agent_username, agent_userrole, numcalls, teamsorter
'2275', '2013-12-30 15:37:11', '2013-12-20', 'KARMEN', 'HANNAH', 'HannahCC', 'Front Office', '1', '1'
'2276', '2013-12-30 15:40:12', '2013-12-23', 'KARMEN', 'HANNAH', 'HannahCC', 'Support', '1', '2'
'2277', '2013-12-30 15:40:48', '2013-12-23', 'KARMEN', 'HANNAH', 'HannahCC', 'Front Office', '0', '1'
Table 2: dollars_data
dollarsid, date_added, last_update_date, created_by, transaction_amt, enrollee, transaction_type, order_number, order_created
'21333', '2013-12-30 15:38:39', '2013-12-20', 'HannahCC', '90.00', '1', 'Item List Price', '345', '2013-12-20'
'21334', '2013-12-30 15:40:48', '2013-12-23', 'HannahCC', '-90.00', '-1', 'Item Cancel', '345', '2013-12-20'
'21335', '2013-12-30 15:40:48', '2013-12-23', 'HannahCC', '100.00', '1', 'Item List Price', '999', '2013-12-23'
SQL Statement:
SELECT
t.agent_userrole as agent_userrole,
t.teamlead as teamlead,
t.agent as agent,
t.agent_username as agent_username,
t.numcalls as numcalls,
d.created_by as created_by,
date_format(d.last_update_date,'%Y-%m-%e') as last_update_date,
sum(d.transaction_amt) as transaction_amt,
sum(d.enrollee) as enrollee,
t.teamsorter as teamsorter
FROM callconversion.dollars_data d, callconversion.num_calls_data t
WHERE
t.agent_username=d.created_by
AND d.last_update_date between '2013-12-1' and '2013-12-29'
AND t.date_of_call=d.last_update_date
GROUP BY last_update_date, teamsorter, teamlead, agent_userrole, agent
ORDER BY teamsorter asc, teamlead asc, agent_userrole asc, agent asc, last_update_date asc
Returned output:
agent_userrole, teamlead, agent, agent_username, numcalls, created_by, last_update_date, transaction_amt, enrollee, teamsorter
'Front Office', 'KARMEN', 'HANNAH', 'HannahCC', '1', 'HannahCC', '2013-12-20', '90.00', '1', '1'
'Front Office', 'KARMEN', 'HANNAH', 'HannahCC', '0', 'HannahCC', '2013-12-23', **'10.00', '0'**, '1'
'Registration Support', 'KARMEN', 'HANNAH', 'HannahCC', '1', 'HannahCC', '2013-12-23', **'10.00', '0'**, '2'
Expected/Desired output:
agent_userrole, teamlead, agent, agent_username, numcalls, created_by, last_update_date, transaction_amt, enrollee, teamsorter
'Front Office', 'KARMEN', 'HANNAH', 'HannahCC', '1', 'HannahCC', '2013-12-20', '90.00', '1', '1'
'Front Office', 'KARMEN', 'HANNAH', 'HannahCC', '0', 'HannahCC', '2013-12-23', **'-90.00', '-1'**, '1'
'Registration Support', 'KARMEN', 'HANNAH', 'HannahCC', '1', 'HannahCC', '2013-12-23', **'100.00', '1'**, '2'
It appears the transaction_amt and enrollee columns are getting summed up in a way I would not expect, based on the GROUP BY values. It appears to be summed up by the last_update_date, but not taking into account the agent_userrole column which has the differentiating factor.