Distinct command wont work . (Urgent )

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

SQL Server 2012 Forums

Transact-SQL (2012)

Distinct command wont work . (Urgent )

Author

Topic

usafelix
Posting Yak Master

165 Posts

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

Dear Sir,

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

Expect Result =======> Help write a query for this result ?
-------------------------------------------------==============
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 : 21:11:24

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

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

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-15 : 23:14:29

I am using your query but the trx_no is still duplicate appear and no sum up the redemption ?
------------------------------------------------------------------
select distinct
s.trx_no,
s.trx_acc_amt,
sum(trx_dat.trx_sub_disamt) Redemption ,

(SELECT emg_name FROM user_hdr WHERE (user_id = s.salesman_code)) AS chinese_name,
(SELECT user_pos FROM user_hdr AS user_hdr_1 WHERE (user_id = s.salesman_code)) AS position,
round(convert(int, CAST(trx_dat.trx_sub_disamt /trx_dat.item_qty AS numeric(36, 2)))/ NULLIF (s.trx_acc_amt, 0)*100,0) as ????

from trx_hdr s
inner join trx_dat on trx_dat.trx_no = s.trx_no
group by s.trx_no, trx_acc_amt, trx_dat.trx_sub_disamt,s.salesman_code,trx_dat.item_qty
---------------------------------------------------------------
Result
trx_no| trx_acc_amt | sub_disamt | position | qty | consumption%
cb3007| $300 | $500 | consutlant| 11 | x%
cb3007| $300 | $200 | consutlant| 11 | x%
cb3007| $300 | $600 | consutlant| 11 | x%
cb3007| $300 | $500 | consutlant| 11 | x%
cb3009| $800 | $200 | consutlant| 11 | x%
cb3009| $800 | $100 | consutlant| 11 | x%
-----------------------------------------------------------------
expect result
trx_no| trx_acc_amt | sub_disamt | position | qty | consumption%
cb3007| $300 | $1800 | consutlant| 11 | x%
cb3009| $800 | $300 | consutlant| 11 | x%

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 12:28:27

Please post your question using this link as the template: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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