Urgent help - edit 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

SQL Server 2012 Forums

Transact-SQL (2012)

Urgent help - edit query

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-18 : 00:13:38

How to change this query to result like this :
I only need the last sum total line. I don't need the in-report sum's

| sku | Trxdate| itemamt | remark
-----------------------------------------------------------
| a | 2012-01-02 | 200 | coupon001 - coupon 002
| a | 2012-01-03 | 100 | coupon324 - coupon 367
| a | 2012-01-05 | 100 |
| b | 2012-01-06 | 200 | coupon 34 - coupon 67
| b | 2012-01-07 | 200 |
| d | 2012-01-07 | 400 |
| e | 2012-01-09 | 500 |
| f | 2012-01-12 | 600 |
| | (Total) | 2300 |
------------------------------------------------------------
select
xsodetail.txdate As Date,
case when xsoheader.shopcode is null
then ' Total'
else
'Shop '+xsoheader.shopcode end as shop,
'$',round(sum(xsopayment.paymentamt),0) as Amount,
xsoheader.depositamt as Deposit_Amount,
xsopayment.paymentcode as Paycode,
xsodetail.memono as Memo,
xsodetail.itemamt as sales_amount,
xsodetail.sku as COUPON,
xsodetail.itemamt as amount,
xsodetail.salesqty as Qty,
xsoremark.remark as Remark

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsoremark on xsoremark.shopcode+xsoremark.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.memono,xsodetail.sku,xsodetail.salesqty,xsoremark.remark
with rollup

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-18 : 11:17:03

You mean you just need the total sum for itemamt?

SUM(xsodetail.itemamt) as amount

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources