Trying to find the difference between two sub-tota

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)

Trying to find the difference between two sub-tota

Author

Topic

Darts75
Starting Member

27 Posts

Posted - 2014-05-23 : 01:34:56

Hi Everyone,

I have the following code which essentially lists the total sales for an items on the first row, and the total credits for the same item on the second row.


SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales'
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode

UNION ALL

SELECT T1.ItemCode, SUM(T1.LineTotal) as 'Total Sales'
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode

The results of the query are shown below (with some alterations for confidentiality).

[url]http://oi58.tinypic.com/29nuycj.jpg[/url]

What I would like to do is write a code block that subtracts the total credits from the total sales, leaving me with only one row of data for the ItemCode.

If anybody can help with writing the code to achieve this it will be greatly appreciated.

Kind Regards,

Davo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-23 : 01:49:14

[code]SELECT ItemCode, SUM([Total Sales])
FROM
(
SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales'
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode

UNION ALL

SELECT T1.ItemCode, -SUM(T1.LineTotal) as 'Total Sales'
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode
) as T
GROUP BY ItemCode[/code]

KH
[spoiler]Time is always against us[/spoiler]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-27 : 13:39:11

[code]

SELECT
ISNULL(sales.ItemCode, returns.ItemCode) AS ItemCode,
ISNULL(sales.total_sales, 0) - ISNULL(returns.total_returns, 0) AS NetSales
FROM (
SELECT T0.ItemCode, SUM(T0.LineTotal) as total_sales
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode
) AS sales
FULL OUTER JOIN (
SELECT T1.ItemCode, SUM(T1.LineTotal) as total_returns
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode
) AS returns ON
returns.ItemCode = sales.ItemCode
ORDER BY
ItemCode

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources