SQL several inner joins cause wrong SUM Result

An example of my Database: (SQLFiddle link at bottom)

I have several Tables:

Table Login

1. User_ID (int)
2. Email (varchar)
3. Passwort
4. Seassion ID

Table Business

5. ID_Login_ID_Business (int)

6. NameOfCompany (varchar)

7. NameContact(varchhar)

8. ID_Location_FK (int)

9. ID_BalancesheetInput_FK (int)

10. ID_Balancesheetoutput(int)

Table ID_BalancesheetInput

11. ID

12. FK_Business_ID (int)

13. RessourceName (varchar50)

14. Amount (decimal)

15. Unit_Fk (int)

16. TypeOfRessource_FK(int)

Table Units

17. Unit_id

18. Unit

Table TypeOfRessource

19. ID_Type

20. Type

Sql should check all entries from Buisness where ID_Login_ID_Business(fk) equals the FK_Business_ID in BalancesheetInput, sum all amounts with the same RessourceNam,e and should simply add the typeofRessource checking the right Unit.

Example:

Table Business

ID, NameOfCompany, ID_BalancesheetInput_FK

• 1 Apples 1
• 2 Minisoft 2

Table BalancesheetInput

ID, FK_Business_ID, RessourceName, Amount, Unit_FK, TypeOFRessource_FK

• 1, 1, electricity, 100, 3, 2
• 2, 1, thermal energy, 200, 3, 2
• 3, 1, thermal energy, 150, 3, 2
• 4, 1, light fuel oil, 10,3,2
• 5, 1, Screws, 200, 3, 2
• 6, 1, Water, 200,4, 3

Table Units

Unit ID, Unit

• 1, pieces
• 2, Ton
• 3, kWh
• 4, m²
• 5, litre

Table TypeOFRessource

ID_Type, Type

• 1, Asset
• 2, Energie
• 3, Material

The following statment tries to catch the Ressources of the selected Business and calculate entries with the same name, such as electricity. They belong to one Company, but something is going wrong.

Everytime I use my sql Statment:

"SELECT DISTINCT "
           . "BalancesheetInput.RessourceName AS Rn, "
           . "Units.Unit AS En, "
           . "SUM(BalancesheetInput.Amount) AS TotalAmount "
           . "FROM Business"
           . "INNER JOIN BalancesheetInput ON FK_Business_ID = 1 "
           . "INNER JOIN Units ON Unit_FK = Unit_ID "
           . "INNER JOIN TypeOfRessource ON TypeOf Ressources = 2 "
           . "GROUP BY BalancesheetInput.RessourceName ");

Result:

light fuel oil 60 kWh Energie – electricity 600 kWh Energie – thermal energy 2100 kWh

Looks like he added 6 times the amount of each row.

What I want as a result:

light fuel oil 10 kWh Energie – electricity 100 kWh Energie – thermal energy 350 kWh

Here is an SQLFiddle with a working version of all this.

If you need further information, just say what you need to help me solve the problem.

Answer

Luk Storms and I came to the same answer; here’s the SQLFiddle I made.

Tips: If you SELECT * on your JOIN, you’ll see that it’s creating a table with six rows for each BalancesheetInput; one for each combination of Name of Company and Energy Type. We used WHERE to narrow down the duplicate rows. This is usually better than SELECT DISTINCT.

Also, “Screws” is miscategorized as “Energie” instead of “Materiel”.