# Displaying balance resulting from two different tables

### คำถาม

• I have 4 tables:

*department(dept_ID, dept_short_name)

*costCentre(cc_ID, dept_ID, costCentre_name)

*monthlyIncome(income_ID, cc_ID, month, amount)

*utilization(util_ID, cc_ID, month, amount)

One department can have many cost centres. So these cost centre will have their income every month and it will be stored in monthlyIncome table by month. So basically monthlyIncome can have many incomes for just one particular cost centre. Meanwhile, utilization table works in same way but value will be stored when income is used.

So what im trying to do is actually to display the balance available for a particular cost centre to the user. So what it have to do is to calculate the sum in monthlyIncome and minus the sum in utilization according to costCentre and display it in dedicated row as per the related costCentre.

Example:

Department       |     Cost Centre     | Balance Income

HR                            1                        23.00<----Value here is the sum(monthlyIncome) - sum(utilization), dedicated value for row

2                        26.00

3                        45.00

IT                            4                        34.00

5                        22.00

Currently my sql statement is as below but it is not yielding as i expect;

"SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department d, costCentre c, monthlyIncome m, utilization u
WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"

Hope im clear here, any idea how to make it possible?

30 เมษายน 2555 2:03

### คำตอบ

• ```;With cteInc As
(Select cc_ID, Sum(amount) As Amount
From monthlyIncome
Group By cc_ID),
cteUtl As
(Select cc_ID, Sum(amount) As Amount
From utilization
Group By cc_ID)
Select d.Dept_Short_name,
c.CostCentre_name,
Coalesce(i.Amount,0) - Coalesce(u.Amount,0) As Balance
From department d
Inner Join costCentre c On d.dept_ID = c.dept_ID
Left Join cteInc i On c.cc_ID = i.cc_ID
Left Join cteUtl u On c.cc_ID = u.cc_ID;
```

Tom
• เสนอเป็นคำตอบโดย 30 เมษายน 2555 2:36
• ทำเครื่องหมายเป็นคำตอบโดย 8 พฤษภาคม 2555 15:35
30 เมษายน 2555 2:16

### ตอบทั้งหมด

• ```;With cteInc As
(Select cc_ID, Sum(amount) As Amount
From monthlyIncome
Group By cc_ID),
cteUtl As
(Select cc_ID, Sum(amount) As Amount
From utilization
Group By cc_ID)
Select d.Dept_Short_name,
c.CostCentre_name,
Coalesce(i.Amount,0) - Coalesce(u.Amount,0) As Balance
From department d
Inner Join costCentre c On d.dept_ID = c.dept_ID
Left Join cteInc i On c.cc_ID = i.cc_ID
Left Join cteUtl u On c.cc_ID = u.cc_ID;
```

Tom
• เสนอเป็นคำตอบโดย 30 เมษายน 2555 2:36
• ทำเครื่องหมายเป็นคำตอบโดย 8 พฤษภาคม 2555 15:35
30 เมษายน 2555 2:16
• Check this blog post in addition to Tom's answer

# Aggregates with multiple tables

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

30 เมษายน 2555 2:37
• Im trying to imply it into a site where user will just have to choose a department from a dropdownlist. From the selected department, the given result should be displayed.

I have tried @Tom Cooper's suggestion but it is not accepting since its not starting from select, delete, insert, update or procedure?

• แก้ไขโดย 30 เมษายน 2555 2:44
30 เมษายน 2555 2:41
• You can make a slight modification in this solution by using derived tables instead of CTE, e.g.

```Select d.Dept_Short_name,
c.CostCentre_name,
Coalesce(i.Amount,0) - Coalesce(u.Amount,0) As Balance
From department d
Inner Join costCentre c On d.dept_ID = c.dept_ID
Left Join (Select cc_ID, Sum(amount) As Amount
From monthlyIncome
Group By cc_ID) i On c.cc_ID = i.cc_ID
Left Join
(Select cc_ID, Sum(amount) As Amount
From utilization
Group By cc_ID)
u On c.cc_ID = u.cc_ID;```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

30 เมษายน 2555 2:43
• I think the structure is fine, but any idea why its giving error on asp.net platfrom(IErrorInfo.GetDescription failed with E_FAIL(0x80004005).) ?

• แก้ไขโดย 30 เมษายน 2555 4:19
30 เมษายน 2555 4:17
• Post the relevant code from ASP.NET and the actual error. The SQL itself is fine and you can test it separately in SSMS first.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

30 เมษายน 2555 12:51
• I have tried to test it with ms access. When i run the query, i get this error:

Syntax error (missing operator) in query expression 'd.dept_ID = c.dept_ID
Left Join (Select cc_ID, Sum(amount) As Amount From monthlyIncomeGroup By cc_ID) i On c.cc_ID = i.cc_ID
Left Join (Select cc_ID, Sum(amount) As Amount From utilization Group By cc_ID)
u
On c.cc_ID = u.cc_ID'

2 พฤษภาคม 2555 3:46
• Are you using Access database? The syntax we provide work in SQL Server (as this is Transact-SQL forum). For Access you may need to do some slight adjustments in the code. You may want to to ask this question in Access specific forum.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

2 พฤษภาคม 2555 12:16