CODEWITHSUNDEEP
sql-server
anonymous23
anonymous23

Reputation: 55

SQL Joining table and minus based on column data

I have 3 tables,

budget:

enter image description here

budgetexpense:

enter image description here

expense:

enter image description here

What the table means is basically, a user will insert a budget amount for a specific expense category in budgetexpense. Then, the sum of this value will be inserted into the budgetamount column in the budget table. Then, the user will be able to add expenses based on specific categories in expense table.

What I want to know, if there is a way to join both budgetexpense and expense table, and be able to minus the amount from budgetexpense with expense based on their categories. For example, transport from budgetexpense table must only minus with transport from expense table.

Apologies if my explanation is a little messy. Let me know and I'll try to explained to you again better.

Upvotes: 0

Views: 74

Answers (2)

Isaac
Isaac

Reputation: 3363

Here is another approach that will show the remaining budget even if there were no expenses. Basically, aggregate the expense amounts on expense category and then join budgetexpense to it on category subtracting total expenses from the budget expense amount.

SELECT
          bexp.*
        , bexp.amount - COALESCE(bcexp.totalexpenseamount, 0) AS [remainingbudgetamount]
FROM      budgetexpense bexp
LEFT JOIN (
              SELECT
                       budgetcategory
                     , SUM(expenseamount) AS [totalexpenseamount]
              FROM     expense
              GROUP BY budgetcategory
          )              bcexp ON bexp.expensecategory = bcexp.budgetcategory

Full demo here.

If you want to also show records where there is an expense with no corresponding record in the budgetexpense table you need to use a FULL JOIN. I don't know exactly what you want for columns I took a guess; change it up as you need. I also added a join on budgetid which you will need if you have more than one budget.

SELECT
          COALESCE(bexp.budgetid, bcexp.budgetid) AS [budgetid]
        , COALESCE(bexp.expensecategory, bcexp.budgetcategory) AS [category]
        , bexp.amount AS [budgetamount]
        , bcexp.totalexpenseamount
        , COALESCE(bexp.amount, 0) - COALESCE(bcexp.totalexpenseamount, 0) AS [remainingbudgetamount]
FROM      budgetexpense bexp
FULL JOIN (
              SELECT
                       budgetid,
                       budgetcategory
                     , SUM(expenseamount) AS [totalexpenseamount]
              FROM     expense
              GROUP BY budgetid
                     , budgetcategory
          )              bcexp ON bexp.budgetid = bcexp.budgetid
                              AND bexp.expensecategory = bcexp.budgetcategory

Full demo of that here.

Upvotes: 1

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

Try below query:

;with CTE as(select budgetid,budgetcatagory,sum(expenseamount) expenseamount from expense
group by budgetid,budgetcatagory
)
select a.budgetid,a.amount,a.expensecategory,a.amount-cte.expenseamount RemaningAmount
from budgetexpense a,cte where a.budgetid=cte.budgetid
 and cte.budgetcatagory=a.expensecategory

For more clearity Click Here

Upvotes: 0

Related Questions