sum records by Type - 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 2008 Forums

Transact-SQL (2008)

sum records by Type

Author

Topic

mledesma
Starting Member

2 Posts

Posted - 2015-04-24 : 10:41:56

Hello forum,

I'm trying to sum by the tender type. Below is the current and wanted results along with my script. I am a novice with SQL....

Thank you,
Mike

Current RESULTS:
STORE_NO WORKSTATION_NO RTL_TRN_ID TENDER_TYPE_ID GROSS_SALES GROSS_RETURNS
9921 1 7400 1 -0.02 0.00000000
9921 1 7400 1 30.00 0.00000000
9921 1 7401 5 21.98 0.00000000
9921 1 7402 5 9.98 0.00000000
9921 1 7404 1 20.00 0.00000000
9921 1 7404 5 4.98 0.00000000

Wanted RESULTS:
STORE_NO WORKSTATION_NO RTL_TRN_ID TENDER_TYPE_ID GROSS_SALES GROSS_RETURNS

9921 1 7400 1 29.98 0.00000000
9921 1 7401 5 21.98 0.00000000
9921 1 7402 5 9.98 0.00000000
9921 1 7404 1 20.00 0.00000000
9921 1 7404 5 4.98 0.00000000

select
RT.STORE_NO,
RT.WORKSTATION_NO,
LI.RTL_TRN_ID,
TI.TENDER_TYPE_ID,
TI.TENDER_AMOUNT as GROSS_SALES,
sum ( case
when (LI.RETURN_FLG=1)
then (LI.QUANTITY) * (LI.PLU_PRICE)
else 0
end
) as GROSS_RETURNS
from
RTL_TRN_DEV RT
left join SALE_RTRN_LN_ITEM as LI
on (RT.RTL_TRN_ID=LI.RTL_TRN_ID)
and (RT.STORE_NO=LI.STORE_NO)
right join TENDER_LINE_ITEM as TI
on (TI.RTL_TRN_ID=LI.RTL_TRN_ID)
and (TI.STORE_NO=LI.STORE_NO)
where
LI.VOID_FLG = 0
and LI.MRCH_ITEM_FLG = 1
group by
RT.WORKSTATION_NO,
RT.STORE_NO,
LI.RTL_TRN_ID,
TI.TENDER_TYPE_ID,
TI.TENDER_AMOUNT

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-24 : 12:21:21

SUM(TI.TENDER_AMOUNT) and remove TI.TENDER_AMOUNT from the GROUP BY.

Also your outer JOINs should probably just be JOINs.
The WHERE clause already converts the LEFT JOIN to a JOIN and the outer part of the RIGHT JOIN looks pointless.

mledesma
Starting Member

2 Posts

Posted - 2015-04-27 : 13:59:44

quote:
Originally posted by Ifor

SUM(TI.TENDER_AMOUNT) and remove TI.TENDER_AMOUNT from the GROUP BY.

Also your outer JOINs should probably just be JOINs.
The WHERE clause already converts the LEFT JOIN to a JOIN and the outer part of the RIGHT JOIN looks pointless.

I made the changes and understand what you are saying. Thank you very much!

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources