CODEWITHSUNDEEP
sqljoincountgroup-bymultiple-tables
user5189702
user5189702

Reputation: 41

How to Correctly Join and Count In Multi Join Query

My main table, Properties, consists of:

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

I have two lookup tables:

Communities Table:

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

Sections Table:

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

I am attempting to summarize the count of lot by community section and return the community id, community name, and section name with the results like:

|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     |

Here is the query I built:

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

It looks like it runs correctly until I check the totals of numLots manually and they are not correct. Some are high, others are low. The interesting thing is that if you sum all of the data this query produces the total number of lots is in fact the same as the total lots in the properties table.

I can't figure out what is wrong with this query.

Any suggestions?

Upvotes: 0

Views: 32

Answers (1)

user5189702
user5189702

Reputation: 41

Discovered my own mistake. I needed to group on both community_code and section_code.

Here is the correct query:

        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

Upvotes: 1

Related Questions