remove duplicate row Group by and rollup

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)

remove duplicate row Group by and rollup

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-22 : 13:03:18

Please edit my query how to remove the duplicate row ? attached of result in below
-------------------------------------------
select
isnull(xsodetail.txdate,'') As Date,
case when xsoheader.shopcode is null
then ' Total'
else
'Shop '+xsoheader.shopcode end as shop,
round(sum(isnull(xsopayment.paymentamt,0)),0) as Amount,
sum(isnull(xsoheader.depositamt,0)) as Deposit_Amount,
isnull(xsopayment.paymentcode,'') as Paycode,
isnull(max(xsodetail.sku),'') as COUPON,
sum(isnull(xsodetail.itemamt,0)) as amount,
sum(isnull(xsodetail.salesqty,0)) as Qty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsopayment.shopcode+xsopayment.memono = xsoheader.shopcode+xsoheader.memono
where (xsodetail.sku ='L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N'
group by xsodetail.txdate,xsoheader.shopcode, xsoheader.depositamt,xsopayment.paymentcode,xsodetail.itemamt,xsodetail.sku,xsodetail.salesqty
with rollup

-----------------------------------------------------------
current result :
Date,shop, deposit amount payment sku pay_amt qty

------------------------------------------------------------
20140805 Shop TS502 $ 5000.00 0.00 EPS L000254 5000.00 1.00
20140805 Shop TS502 $ 5000.00 0.00 EPS L000254 5000.00 1.00
20140805 Shop TS502 $ 5000.00 0.00 EPS L000254 5000.00 1.00
20140805 Shop TS502 $ 5000.00 0.00 EPS L000254 5000.00 1.00
20140805 Shop TS502 $ 5000.00 0.00 L000254 5000.00 1.00
20140805 Shop TS502 $ 5000.00 0.00 L000254 5000.00 1.00
20140805 Total $ 5000.00 0.00 L000254 5000.00 1.00
20140806 Shop MC501 $ 100.00 100.00 MOP L000254 10000.00 2.00
20140806 Shop MC501 $ 100.00 100.00 MOP L000254 10000.00 2.00
20140806 Shop MC501 $ 100.00 100.00 MOP L000254 10000.00 2.00
20140806 Shop MC501 $ 100.00 100.00 MOP L000254 10000.00 2.00
20140806 Shop MC501 $ 100.00 100.00 L000254 10000.00 2.00
20140806 Shop MC501 $ 100.00 100.00 L000254 10000.00 2.00
20140806 Total $ 100.00 100.00 L000254 10000.00 2.00

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-22 : 13:33:39

It would be easier if you had included table DDL and some row data

Why do you have your aggregates in your Group by?

select
isnull(xsodetail.txdate,'') As Date,
case when xsoheader.shopcode is null
then ' Total'
else
'Shop '+xsoheader.shopcode end as shop,
round(sum(isnull(xsopayment.paymentamt,0)),0) as Amount,
sum(isnull(xsoheader.depositamt,0)) as Deposit_Amount,
isnull(xsopayment.paymentcode,'') as Paycode,
isnull(max(xsodetail.sku),'') as COUPON,
sum(isnull(xsodetail.itemamt,0)) as amount,
sum(isnull(xsodetail.salesqty,0)) as Qty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsopayment.shopcode+xsopayment.memono = xsoheader.shopcode+xsoheader.memono
where (xsodetail.sku ='L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N'
group by xsodetail.txdate,xsoheader.shopcode
with rollup

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-22 : 14:27:01

Dear Sir,
I am simple request output is sort by shop + date + sku and show the grand total amount and qty in last row to display with no duplicate record. How I can delete the replicate row ?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources