PostgreSQL GROUP BY聚合字段问题：指定年月Personal CC求和求助

Hey, let's break down what's going wrong here and fix it up!

The Core Issue #

Your current query is grouping on too many granular fields, which is why you're getting incorrect or duplicated results instead of a single monthly summary per sponsor:

• You added mcc.personal_cc_mtd__c to your GROUP BY—this is the field you're trying to sum up, so grouping by it splits your results into every unique individual CC value, completely defeating the purpose of summing.
• You also included extended_downline."Same Month" in GROUP BY, but you want to sum this field to get the total number of assistant supervisors. Grouping by it will instead create a separate row for every distinct value of "Same Month" per sponsor.

Fixed Query #

Here's the adjusted code that gives you a single monthly summary per sponsor, with correct sums and sorting:

SELECT
    extended_downline."Sponsor FBO ID",
    extended_downline."Sponsor Name",
    mcc.processing_month__c AS "Processing Month",
    mcc.processing_year__c AS "Processing Year",
    SUM(mcc.personal_cc_mtd__c) AS "Personal CC",
    SUM(extended_downline."Same Month") AS "Number of Assistant Supervisors in Same Month",
    BOOL_OR(mcc.current_month_active__c) AS "Current Month Active"
FROM extended_downline
LEFT OUTER JOIN connect.monthly_cc__c mcc
    ON mcc.distributor__c = extended_downline.sponsor_distributor__c
    AND mcc.op_company__c = extended_downline."Operating Company"
    AND mcc.processing_year__c = '2017'
    AND mcc.processing_month__c = 10
WHERE
    extended_downline."Same Month" <> 0
    AND extended_downline."Generation" <> 0
    AND mcc.personal_cc_mtd__c IS NOT NULL
GROUP BY
    extended_downline."Sponsor FBO ID",
    extended_downline."Sponsor Name",
    mcc.processing_year__c,
    mcc.processing_month__c
ORDER BY "Personal CC" DESC
LIMIT 20;

What Changed & Why #

1. Trimmed the GROUP BY clause: Removed mcc.personal_cc_mtd__c and extended_downline."Same Month"—now we only group by fields that uniquely identify a sponsor's monthly record (sponsor ID/name plus year/month).
2. Cleaned up the JOIN filter: Moved the 2017-10 date check directly into the JOIN condition (no need for extra parentheses) to ensure we only pull relevant monthly CC data.
3. Clarified the sum for assistant supervisors: Explicitly sums extended_downline."Same Month" across all matching rows for each sponsor, giving you the correct total count.

This will give you exactly one row per sponsor for October 2017, with the summed Personal CC, total assistant supervisors, and active status—sorted descending by Personal CC as you wanted.

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