SQL多表左连接与聚合问题：关联后聚合值重复计算

使用SQL多表Left Join进行聚合计算时遇到问题：当关联的子表存在多行数据时，其他子表的聚合值会被重复计算。

示例表结构与数据 #

tblUsers   tblUsersCars   tblUsersHouses
---------  -------------  ------------------
id         idCar          idHouse          
name       qty            qty
lastName   type           type
           idUsers        idUsers 
--------------------------------------------

        tblUsers       |    tblUsersCars   |     tblUsersHouses     |
-----------------------|-------------------|------------------------|
1, Matt, Groening      |   7, 1, red, 1    |   15, 1, "2 Floors", 1 |
2, Steve, Segal        |   8, 2, blue, 1   |                        |
3, Scarlett, Johanson  |                   |                        |
--------------------------------------------------------------------|

预期查询结果 #

id | name | lastName | cars | houses |
---+------+----------+------+--------+
1  | Matt | Groening |   3  |   1    |

实际查询结果 #

id | name | lastName | cars | houses |
---+------+----------+------+--------+
1  | Matt | Groening |   3  |   2    |

可以看到，houses列的数值被重复计算，重复次数等于cars对应子表的行数。示例中tblUsersCars有2行数据，tblUsersHouses仅1行，但结果中houses的总和变为2。

我希望无论各子表行数多少，都能正确计算两个表的qty总和。

当前查询语句 #

SELECT 
    tblUsers.id, tblUsers.name, tblUsers.lastName,
    COALESCE(SUM(tblUsersCars.qty), 0) AS 'cars',
    COALESCE(SUM(tblUserHouses.qty), 0) AS 'houses'
FROM 
    tblUsers
LEFT JOIN 
    tblUsersCars ON tblUsersCars.idUsers = tblUsers.id
LEFT JOIN 
    tblUserHouses ON tblUserHouses.idUsers = tblUsers.id
WHERE
    tblUsers.id = 1
GROUP BY
    tblUsers.id, tblUsers.name, tblUsers.lastName

已通过@nenad的回答解决该问题。

内容的提问来源于stack exchange，提问作者Code_Geass