how to edit this query ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to edit this query ?

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 09:39:24

Dear Sir,

I want sum the redemption amount by each trx_no. Because I am join two table . the sales detail have many duplicate trx_no with carry different amount.Thus ,I want to exclude duplicate data (based on just one column -- an trx_no field). Basically, I want to only have one result per trx_no. The entire row won't be unique, however, because one or more fields will be different.so a DISTINCT won't work because the entire row is not a duplicate.
--------------------------------------------------------
select distinct s.trx_no,s.trx_acc_amt,sum(d.trx_sub_disamt)as Redemption
from trx_hdr s
inner join trx_dat d on d.trx_no = s.trx_no
inner join user_dat u on u.id = s.id
group by s.trx_no,s.trx_acc_amt
-----------------------------------------------------------
trx_no |trx_acc_amt | redemption amt
MKLTD1412001239 |2270 | 1500
MKLTD1412001239 |2270 | 150
CB3SW1501001166 | 0 | -750
CB3SW1501001166 | 0 | 750
T5LSW1408000039 |1200 | 1200
T5LSW1408000039 |1200 | 400
T5LSW1408000039 |1200 | 500

I want to get the result in below.
-------------------------------------------------
trx_no |trx_acc_amt | redemption
MKLTD1412001239 |2270 | $1650
CB3SW15010T5LSW | 0 | 0
T5LSW1408000039 |1200 | $2100
--------------------------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 11:51:47

Please continue the discussion in your other topics rather than starting a new one for the same problem.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources