sql - SQL总和不起作用

我试图将基于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

最佳答案

从列表中删除组中的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