mysql - Sum 和 Group By 问题

Question

我对两个表之间的 SQL 语句以及带有 group by 子句的总和感到很困惑。

表 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'

表 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语句:

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

返回的输出:

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'

预期/期望的输出:

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'

根据 GROUP BY 值，transaction_amt 和 enrollee 列似乎以我不希望的方式汇总。它似乎由 last_update_date 总结，但没有考虑具有区分因素的 agent_userrole 列。

Answer 1

问题似乎是 date_of_call 和 timestamp 似乎只是日期，不包括时间。因此 dollars_data 中具有 last_update_time = 2013-12-23 的每一行都匹配 num_calls_data 中具有 date_of_call = 2013-12- 的两行23，因此您的输出显示两行的总和。 100.00 和 -90.00 由 sum() 加在一起，结果是您得到的 10.00。