How to subtact a Sum Amount from my table a from a value in my table b

Question

tableA:

tranactionid staffid amount 
1001          19052   2000
1002          19043   3000
1004          19076   4000

tableB:

BudgetCode Budget
271098     20000

I want to sum Amount in tableA and subtract it from Budget value 20000

20000 - 9000 = 11000

this is my sql statement: select (SELECT Budget AS Total1 from tableB) - (SELCT SUM(amount) AS Total2 From tableA) AS Result — Nas, Feb 24 '16 at 18:21
What's the problem you're having? Your SQL query seems to work fine, except for the typo `SELCT` (but as @TabAlleman said, it should be edited into your post, not posted as a comment). — Dan Field, Feb 24 '16 at 18:33
Have you taken a look at http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — KornMuffin, Feb 24 '16 at 18:33

score 0 · Answer 1 · edited Feb 24 '16 at 19:30

0

You have to use:

 SELECT (SELECT [Budget] FROM tableB) - (SELECT SUM([amount]) FROM tableA) AS [any_name]

edited Feb 24 '16 at 19:30

Kyle Williamson

answered Feb 24 '16 at 18:58

Niru Tellez

score 0 · Answer 2 · answered Feb 24 '16 at 19:00

0

This will work if TableB has just one row forever, but it's likely your requirements go beyond that:

SELECT tableB.Budget - (SELECT SUM(amount) FROM tableA)
FROM tableB.Budget;

answered Feb 24 '16 at 19:00

Ed Gibbs

2 Answers2