如何在多连接查询中正确连接和计数

Question

我的主表Properties包含：

id (index primary id)
community_code
section_code
lot (index unique)

我有两个查找表：

社区表：

id (index primary id)
code (index unique - relates to properties.community_code)
name

节目表：

id (index primary id)
community_id (index - relates to communities.id)
code (index - relates to properties.section_code)
name (index)

我尝试按社区区段汇总lot计数，并返回社区id、社区名称和区段名称，结果如下：

|communityId|communityCode|communityName|sectionCode|sectionName|numLots|
|1          |CAG          |Community CAG|1          |Section 1  |156    |
|1          |CAG          |Community CAG|2          |Section 2  |44     |
|1          |CAG          |Community CAG|3          |Section 3  |100    |
|2          |CKS          |Community CKS|Q          |Section Q  |102    |
|2          |CKS          |Community CKS|X          |Section X  |78     |
|2          |CKS          |Community CKS|Z          |Section Z  |10     |

下面是我构建的查询：

SELECT 
    c.id as 'communityId',
    p.community_code as 'communityCode', 
    c.name as 'communityName', 
    p.section_code as 'sectionCode', 
    s.name as 'sectionName', 
    COUNT(p.section_code) as 'numLots'
FROM properties_master p
JOIN communities c ON p.community_code = c.code
JOIN sections s ON p.section_code = s.code AND c.id = s.community_id
GROUP BY p.section_code
ORDER BY c.name ASC, s.name ASC

它看起来运行正常，直到我手动检查numLots的总数，但它们不正确。一些是高的，另一些是低的。有趣的是，如果将此查询生成的所有数据相加，则总批次数实际上与属性表中的总批次数相同。

我不知道这个查询出了什么问题。

有什么建议吗？

Answer 1

发现了我自己的错误。我需要在community_code和section_code上进行分组。

下面是正确的查询：

        SELECT 
            c.id as 'communityId',
            p.community_code as 'communityCode', 
            c.name as 'communityName',
            s.id as 'sectionId',
            p.section_code as 'sectionCode',
            s.name as 'sectionName',
            COUNT(p.section_code) as 'numLots'
        FROM properties_master p
        LEFT JOIN communities c ON p.community_code = c.code
        LEFT JOIN sections s ON c.id = s.community_id AND p.section_code = s.code
        GROUP BY p.community_code, p.section_code
        ORDER BY p.community_code ASC, p.section_code ASC