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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments