# Thread: Trying to find the difference between two sub-totals (sales - credits)

1. Registered User
Join Date
Mar 2014
Posts
35

## Unanswered: Trying to find the difference between two sub-totals (sales - credits)

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.

Code:
```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).

Query Result (image)

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

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Are there more than 1 row for an ItemCode(e.g. 'ACR2401010') in INV1 and RIN1?

3. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Do you mean something like:
Code:
```select ItemCode, sum('Total Sales'), sum('Total Credits')
,  sum('Total Sales') - sum('Total Credits')
from (
SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales', 0 as 'Total Credits'
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode

UNION ALL

SELECT T1.ItemCode, 0 as 'Total Sales', SUM(T1.LineTotal) as 'Total Credits'

FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode)
GROUP BY ItemCode```

4. Registered User
Join Date
Mar 2014
Posts
35
Hello Dave,

Thankyou for your kind response, and for taking the time to help.

Kind Regards,

David

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
This might be another solution.

Note: If there was only 1 row for an ItemCode(e.g. 'ACR2401010') in INV1 or RIN1,
then the subquery corresponding to the table might be unneccesary (Just join the table itself).
Code:
```SELECT T0.ItemCode
, 'Total Sales' , 'Total Credits'
, 'Total Sales' - 'Total Credits' as Difference
FROM  (SELECT ItemCode, SUM(LineTotal) as 'Total Sales'
FROM  INV1
WHERE ItemCode = 'ACR2401010'
GROUP BY
ItemCode
) as T0
INNER JOIN
(SELECT ItemCode, SUM(LineTotal) as 'Total Credits'
FROM  RIN1
WHERE ItemCode = 'ACR2401010'
GROUP BY
ItemCode
) as T1
ON   T1.ItemCode = T0.ItemCode
;```

6. Registered User
Join Date
Mar 2014
Posts
35
Hi tonkuma,

Thanks for adding your response to my post. I have ultimately used your style of coding. A quick question (if you don't mind), when creating an alias from a sub-query is it possible to include AS after the closing bracket? E.g.: ) AS T0, instead of just ) T0

Kind Regards,

Davo
Last edited by Darts75; 05-26-14 at 20:03.

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
when creating an alias from a sub-query is it possible to include AS after the closing bracket? E.g.: ) AS T0, instead of just ) T0
Yes!
Please see the Syntax and the Arguments in FROM (Transact-SQL)

Syntax

Code:
```[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ] ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
...
...
...
}```
Arguments

...
...
...

derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.

derived _table can use the Transact-SQL table value constructor feature to specify multiple rows.
For example, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);.

8. Registered User
Join Date
Mar 2014
Posts
35
Great, thank you. I will familiarize myself with the finer details of the FROM clauses syntax.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•