CODEWITHSUNDEEP
sqlsql-serversumaggregate
Ushah
Ushah

Reputation: 41

SQL sum is not functioning

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

Upvotes: 2

Views: 59

Answers (2)

Daniel Marcus
Daniel Marcus

Reputation: 2686

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

Related Questions