SQL sum 不起作用

Question

我试图根据 ID 将总佣金加在一起，但这会产生单独的佣金金额。我需要根据去年提取数据

SELECT
fld.fa_id
,sum(commission_amt) as 'Commission Amount'
,date_id
FROM [dbo].[fa_dim] fa, [dbo].[product_summary] fld
WHERE fa.fa_id = fld.fa_id 
AND fld.date_id > 20170523
GROUP BY  fld.fa_id , fld.commission_amt , date_id
ORDER BY fld.fa_id

我得到的结果如下所示:

ID    Commission
7        50
7        12
7        5
8        20
8        10

我需要它们看起来像这样:

ID      Commission 
7           67
8           30 

Answer 1

删除分组依据列表中的commission_amt列:

SELECT
fld.fa_id
,sum(commission_amt) as 'Commission Amount'
,date_id
FROM [dbo].[fa_dim] fa, [dbo].[product_summary] fld
WHERE fa.fa_id = fld.fa_id 
AND fld.date_id > 20170523
GROUP BY  fld.fa_id ,  date_id
ORDER BY fld.fa_id