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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -