# Summing UP Individual Calculation

• ### Question

• Hi Guys,

I have 2 Tables Namely and Item Master & Item Ledger. The relationship is Item.No = Item Master.Item No.

And for an Item There can be many records in Item Ledger.

In Item Ledger, there are columns called Item No, Transaction Date, Cost Amount(USD), Cost Amount(SGD), Sales Amount(USD)

What i want is for an Item my query goes to Item Ledger based on relationship and transaction date filters and sum up the value based on the calculation as Sales Amount(USD) * Cost Amount(SGD) / Cost Amount(USD).

ALI

Tuesday, March 3, 2015 1:58 PM

• Hi Visakh,

Yes this works but the query isquite complex. Can't I simply use

```Select Item.No,Item.Description,
(Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)

From Item Table As Item```

And Thanks for at least giving the idea how this can be done.

Thank you.

ALI

If a nested subquery is preferable, you may reference the below.

```DECLARE @Item_Master TABLE(Item_No CHAR(1),Description VARCHAR(99))
INSERT INTO @Item_Master VALUES('A','This is A item')
INSERT INTO @Item_Master VALUES('B','This is B item')

DECLARE @Item_Ledger TABLE(Item_No CHAR(1),Transaction_Date DATE, [Cost_Amount(USD)] DECIMAL(10,6) ,
[Cost_Amount(SGD)] DECIMAL(10,6), [Sales_Amount(USD)] DECIMAL(10,6) );

INSERT INTO @Item_Ledger VALUES('A','12-mar-2014',100,126.8457,200);
INSERT INTO @Item_Ledger VALUES('A','19-mar-2014',102,130.509,201);
INSERT INTO @Item_Ledger VALUES('A','14-apr-2014',105,131.922,210);
INSERT INTO @Item_Ledger VALUES('A','15-jun-2014',99,123.741486,200);
INSERT INTO @Item_Ledger VALUES('A','16-jun-2014',99.02,123.745294,200);
INSERT INTO @Item_Ledger VALUES('A','17-jun-2014',100,124.97,201);

DECLARE @StartDate DATE,@EndDate DATE
SET @StartDate='20140101'
SET @EndDate='20140401'
Select Item.ITEM_No,Item.Description,
(Select SUM(IL.[Sales_Amount(USD)] * IL.[Cost_Amount(SGD)] *1.0/NULLIF(IL.[COST_Amount(USD)],0)) From @Item_Ledger As IL
WHERE Item_No = item.Item_No
AND Transaction_Date >= @StartDate
From @Item_Master  As item```

If you have any question, feel free to let me know.

Eric Zhang
TechNet Community Support

• Edited by Thursday, March 5, 2015 9:56 AM
• Marked as answer by Thursday, March 5, 2015 10:34 AM
Thursday, March 5, 2015 9:54 AM

### All replies

• Sorry cannot test it right now..

SELECT ItemNo,

(SELECT SUM(calc) FROM tbl WHERE dt >='20140301'  AND dt <='20140301') sgd,

(SELECT SUM(calc) FROM tbl WHERE dt >='20140401'  AND dt <='2014071') sgd1

FROM tbl

GROUP BY ItemNo

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Tuesday, March 3, 2015 2:17 PM
• ```SELECT m.itemNo,
COALESCE(SalesAmount,0) AS SalesAmount
FROM ItemMaster m
OUTER APPLY (SELECT SUM(([Sales Amount(USD)] * [Cost Amount (SGD)]) * 1.0/NULLIF([Cost Amount (USD)],0)) AS SalesAmount
FROM ItemLedger
WHERE ItemNo = m.ItemNo
AND TransactionDate >= @StartDate
)l```
Where you pass daterange through @StartDate and @EndDate parameters ie 20140301 and 20141231 for Mar 2014 etc

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Tuesday, March 3, 2015 2:47 PM
• Hi Uri,

The Calc is not a column in the Item Ledger Table. I showed that just to clarify that how the value is calculated.

ALI

Wednesday, March 4, 2015 1:49 AM
• Hi Visakh,

Yes this works but the query isquite complex. Can't I simply use

```Select Item.No,Item.Description,
(Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)

From Item Table As Item```

And Thanks for at least giving the idea how this can be done.

Thank you.

ALI

Wednesday, March 4, 2015 1:55 AM
• Hi Visakh,

Yes this works but the query isquite complex. Can't I simply use

```Select Item.No,Item.Description,
(Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)

From Item Table As Item```

And Thanks for at least giving the idea how this can be done.

Thank you.

ALI

If a nested subquery is preferable, you may reference the below.

```DECLARE @Item_Master TABLE(Item_No CHAR(1),Description VARCHAR(99))
INSERT INTO @Item_Master VALUES('A','This is A item')
INSERT INTO @Item_Master VALUES('B','This is B item')

DECLARE @Item_Ledger TABLE(Item_No CHAR(1),Transaction_Date DATE, [Cost_Amount(USD)] DECIMAL(10,6) ,
[Cost_Amount(SGD)] DECIMAL(10,6), [Sales_Amount(USD)] DECIMAL(10,6) );

INSERT INTO @Item_Ledger VALUES('A','12-mar-2014',100,126.8457,200);
INSERT INTO @Item_Ledger VALUES('A','19-mar-2014',102,130.509,201);
INSERT INTO @Item_Ledger VALUES('A','14-apr-2014',105,131.922,210);
INSERT INTO @Item_Ledger VALUES('A','15-jun-2014',99,123.741486,200);
INSERT INTO @Item_Ledger VALUES('A','16-jun-2014',99.02,123.745294,200);
INSERT INTO @Item_Ledger VALUES('A','17-jun-2014',100,124.97,201);

DECLARE @StartDate DATE,@EndDate DATE
SET @StartDate='20140101'
SET @EndDate='20140401'
Select Item.ITEM_No,Item.Description,
(Select SUM(IL.[Sales_Amount(USD)] * IL.[Cost_Amount(SGD)] *1.0/NULLIF(IL.[COST_Amount(USD)],0)) From @Item_Ledger As IL
WHERE Item_No = item.Item_No
AND Transaction_Date >= @StartDate