# Thread: Stuck with this probably simple query

1. Registered User
Join Date
Jun 2012
Posts
3

## Unanswered: Stuck with this probably simple query

Hello, I've got his query
SELECT Stock.Stock_ID, Stock.Amount_Units FROM Stock
Code:
```which outsputs likes this	// every item in the stock table, with an amount
Stock_ID	Amount_Units
1              2
2		1
3		1
4		5
5		1
Etc

and ive got this query..	// Items that have been sold, with an amount
SELECT Items_Sold.Stock_ID, Items_Sold.Amount_Units

Which outputs like this
Stock_ID	Amount_Units
4		4
5		1

How can i converge these two together, so i see how many of each Stock Item is left?

Pseudo code --
Foreach row in the Query 1, Match it too Query 2
Then Foreach row that matches, DO (query1.Amount_Units - query2.Amount_Units)

E.g

Stock_ID	Amount_Units	Units_Left
1		2			2
2		1			1
3		1			1
4		5			1 // Affected
5		1			0 // Affected```
Any help would be greatly appreciated!

/Insidus

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```SELECT Stock.Stock_ID
, Stock.Amount_Units
, COALESCE(Items_Sold.Amount_Units,0) AS Sold_Units
, Stock.Amount_Units -
COALESCE(Items_Sold.Amount_Units,0) AS Units_Left
FROM Stock
LEFT OUTER
JOIN Items_Sold
ON Items_Sold.Stock_ID = Stock.Stock_ID```

3. Registered User
Join Date
Jun 2012
Posts
3
Thanks mate, that's absolutely brilliant!
There is however, one small problem, which i probably should have mentioned.

In the Items_Sold Table, there may be two records for the same item.
Code:
```Stock_ID       Amount_Units // Sold
44                5
44                2```
Is there a way to group them? So Stock_ID of 44 = Amount_Units 7,

/Insidus

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Insidus
Is there a way to group them?
Code:
```SELECT Stock.Stock_ID
, Stock.Amount_Units
, COALESCE(SUM(Items_Sold.Amount_Units),0) AS Sold_Units
, Stock.Amount_Units -
COALESCE(SUM(Items_Sold.Amount_Units),0) AS Units_Left
FROM Stock
LEFT OUTER
JOIN Items_Sold
ON Items_Sold.Stock_ID = Stock.Stock_ID
GROUP
BY Stock.Stock_ID
, Stock.Amount_Units ```

#### Posting Permissions

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