Search code examples
sqlsql-serversuminner-joinsql-server-2019

Wrong Result With Sum Function When Use Inner Join In SQL Server

i have two Tables;

Human
-----------
id
Name
list<car> cars

Car
-----------
id
name
price
Human

I want the total price of a human cars,so i used this SQL code for this:

SELECT
SUM(Price)
FROM Cars
INNER JOIN Humans ON Cars.Human_id = 1

but result is wrong.

for example:

id name Price Human_id
1 car1 10000 1
2 car2 15000 1

When I use that code for this table, the result is 50,000! But the answer must be 25,000. It seems that this mistake has something to do with the number of registered cars. can you explain why this wrong happen with sum function and inner join and calculate numbers multiple?

Solution 
  • SELECT
SUM(Price)
FROM Cars
INNER JOIN Humans ON Cars.Human_id = 1

    This a cartesian product between all Humans and Human 1's cars.

    You probably want

    SELECT
SUM(Price)
FROM Cars
INNER JOIN Humans 
 ON Cars.Human_id = Human.Id 
WHERE Human.Id = 1