No duplicate trx_no - 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

No duplicate trx_no

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 05:14:16

Dear Sir,

How to change below query and get the output data and not allow duplicate trx_no !!
=============================================
select distinct
user_dat.title,
s.trx_no,
s.trx_acc_amt,
sum(trx_dat.trx_sub_disamt) Redemption
from trx_hdr s
inner join trx_dat on trx_dat.trx_no = s.trx_no
inner join user_dat on user_dat.id = s.id
group by
s.trx_no,
s.trx_acc_amt,
trx_dat.trx_sub_disamt
-----------------------------------------------------------------
Current output data
trx_no |trx_acc_amt| redemption
MKLTD1412001239 |2270 | 1500
MKLTD1412001239 |2270 | 150
CB3SW1501001166 | 0 | -750
CB3SW1501001166 | 0 | 750
T5LSW1408000039 |1200 | 1200
T5LSW1408000039 |1200 | 400
T5LSW1408000039 |1200 | 500
------------------------------------------------
I want and desire output data in below
-------------------------------------------------
trx_no |trx_acc_amt| redemption
MKLTD1412001239 |2270 | 1650
CB3SW15010T5LSW | 0 | 0
T5LSW1408000039 |1200 | 2100
------------------------------------------------

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-02-12 : 05:21:51

I don't see any column from the users table in your output.
can you try this query?
select
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

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 05:46:51

Dear Sir,
This query is running 2 -3 times but the result is same. 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 price.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.

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
------------------------------------------------

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 21:00:16

Anyone can help to edit this simple query for output this result ?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-12 : 21:34:08

what is the result of you original query?

select distinct
user_dat.title,
s.trx_no,
s.trx_acc_amt,
sum(trx_dat.trx_sub_disamt) Redemption
from trx_hdr s
inner join trx_dat on trx_dat.trx_no = s.trx_no
inner join user_dat on user_dat.id = s.id
group by
s.trx_no,
s.trx_acc_amt,
trx_dat.trx_sub_disamt

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-13 : 07:44:50

this is original result ....................
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
-------------------------------------------------------------
Expect result
-------------------------------------------------

trx_no |trx_acc_amt | redemption
MKLTD1412001239 |2270 | $1650
CB3SW15010T5LSW | 0 | 0
T5LSW1408000039 |1200 | $2100

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 09:22:34

Don't group by s.trx_acc_amt. instead change your select for that columm to max(s.trx_acc_amt) trx_acc_amt

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-17 : 01:00:30

Instead of Joins you can use union all operator

Select X. Trx_no,sum(X.redemption_amt) as redemption_amt from
(
Select TR.trx_no,sum(TR.redemption_amt)as redemption_amt from TestTaxRedemption TR group by Tr.trx_no
union all
Select TR1.trx_no,sum(TR1.redemption_amt) as redemption_amt from TestTaxRedemption_1 TR1 group by TR1.trx_no
)
as X group by X.Trx_no

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources