SQL sum is not functioning

Question

I am trying to add together total commissions based on ID's, however it is resulting in separate commission amounts. I need to pull data based on the last year

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

the results I'm getting look like this:

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

I need them to look like this:

ID      Commission 
7           67
8           30

You should avoid the ANSI-89 join syntax, too: sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Nick
– Nick, Commented May 24, 2018 at 19:05

Barbaros Özhan · Accepted Answer · 2018-05-24 19:09:38Z

4

remove commission_amt column in the group by list :

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

edited May 24, 2018 at 19:09

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered May 24, 2018 at 19:02

Daniel Marcus

2,6841 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-05-24 19:02:43Z

You have too many columns in the group by and the select:

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

The group by should contain the keys that uniquely define each row in the result set. Because you want one row for each fld.fa_id, that is the only column needed in the GROUP BY.

Notice that I also fixed the JOIN syntax. Learn to use proper, explicit, standard JOIN syntax. It is also a bad idea to put column aliases in single quotes. The escape character in SQL Server are the square braces. Better yet are column aliases that do not need to be escaped.

Collectives™ on Stack Overflow

SQL sum is not functioning

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related