基于另一计数的MySQL统计问题：筛选拥有2+服务的收件人

Hey there! Let's fix up your SQL to meet the requirement of only counting recipients who have 2 or more services in tbl_recipient_services. First, let's break down the issues in your original query and then build the corrected version step by step.

Key Issues in the Original Query #

• The unqualified INNER JOIN on tbl_gender and tbl_age_range creates a Cartesian product (all possible combinations of gender, age range, and service) which will give incorrect counts.
• There's a typo: g.gander_id should be g.gender_id.
• The age range check is placed in the JOIN condition with tbl_recipient instead of properly linking to tbl_age_range.

Solution Approach #

First, we need to identify recipients who have 2+ services by grouping tbl_recipient_services and filtering with HAVING. Then, we'll use this list of qualified recipients to narrow down our main query.

Corrected SQL Query #

SELECT 
    s.service_name,
    s.service_id,
    g.gender_id,
    ag.age_range_id,
    COUNT(DISTINCT r.recipient_id) AS recipient_count
FROM 
    tbl_services s
-- Link services to recipient-service mappings
JOIN 
    tbl_recipient_services rs ON rs.service_id = s.service_id
-- Filter to only recipients with 2+ services
JOIN 
    (
        SELECT recipient_id
        FROM tbl_recipient_services
        GROUP BY recipient_id
        HAVING COUNT(DISTINCT service_id) >= 2
    ) qualified_recipients ON rs.recipient_id = qualified_recipients.recipient_id
-- Link to recipient details
JOIN 
    tbl_recipient r ON r.recipient_id = rs.recipient_id
-- Link to gender (directly from recipient's gender ID)
JOIN 
    tbl_gender g ON g.gender_id = r.gender_id
-- Link to age range by matching recipient's age to min/max age in the range
JOIN 
    tbl_age_range ag ON (YEAR(SYSDATE()) - YEAR(r.recipient_birth_date)) BETWEEN ag.min_age AND ag.max_age
-- Group by all non-aggregated columns to get correct counts per group
GROUP BY 
    s.service_id, s.service_name, g.gender_id, ag.age_range_id

Breakdown of Changes #

1. Qualified Recipients Subquery: The inner query first pulls all recipients who have 2 or more distinct services. Using COUNT(DISTINCT service_id) ensures we don't count duplicate service entries for the same recipient.
2. Proper Table Joins: We've restructured joins to avoid Cartesian products, linking each table with valid foreign key relationships.
3. Age Range Matching: The age check is now part of the JOIN with tbl_age_range, ensuring we only match recipients to the correct age range they belong to.
4. Distinct Count: COUNT(DISTINCT r.recipient_id) prevents overcounting if a recipient appears multiple times in tbl_recipient_services for the same service.

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