如何通过PatientApplicationId关联表，GROUP BY求和并获取用户名

我拥有两张数据表：Client_BurnOuts（存储收支明细）和PatientPortalLogins（存储用户登录信息）。需要对Client_BurnOuts的多列数据分别分组求和，得到TotalIncome（总收入）和TotalExpenses（总支出），同时展示对应用户的LoginID（用户名）。

尝试过两个查询：

1. 关联表查询：

select Pa.LoginID, Sum(CB.AFDC+CB.ChildSupport+CB.FoodStamps+CB.OtherIncome+CB.WagesSalary+CB.VeteransBenefit+CB.SocialSecurity+CB.Retirement+CB.GeneraAssistance) as TotalIncome ,
        
Sum(CB.WaterSewer+CB.Transportation+CB.HouseRent+CB.FoodCost+CB.Electricity+CB.DoctorVisit+CB.ChildCare+CB.CarPayment+CB.OtherExpense) as TotalExpences

 from Client_BurnOuts CB
        
    Join PatientPortalLogins Pa on CB.PatientApplicationId = Pa.PatientApplicationId 
        group by Pa.PatientApplicationId

2. 单独求和查询：

select PatientApplicationId, Sum(AFDC+ChildSupport+FoodStamps+OtherIncome+WagesSalary+VeteransBenefit+SocialSecurity+Retirement+GeneraAssistance) as TotalIncome ,
Sum(WaterSewer+Transportation+HouseRent+FoodCost+Electricity+DoctorVisit+ChildCare+CarPayment+OtherExpense) as TotalExpences
from Client_BurnOuts
group by PatientApplicationId

但目前仅能单独获取分组后的求和结果，无法正确同时获取用户名信息，请问如何调整查询以实现需求？

方法一：先分组求和再关联用户表（推荐） #

先在子查询中完成Client_BurnOuts的分组求和，确保每个PatientApplicationId只对应一条合计数据，再关联PatientPortalLogins获取用户名。这种方式能避免因两张表一对多关联导致的求和结果重复计算问题：

SELECT 
    Pa.LoginID,
    IncomeSummary.TotalIncome,
    IncomeSummary.TotalExpenses
FROM (
    -- 子查询：对收支表按PatientApplicationId分组求和
    SELECT 
        PatientApplicationId,
        SUM(AFDC + ChildSupport + FoodStamps + OtherIncome + WagesSalary + VeteransBenefit + SocialSecurity + Retirement + GeneraAssistance) AS TotalIncome,
        SUM(WaterSewer + Transportation + HouseRent + FoodCost + Electricity + DoctorVisit + ChildCare + CarPayment + OtherExpense) AS TotalExpenses
    FROM Client_BurnOuts
    GROUP BY PatientApplicationId
) AS IncomeSummary
-- 关联用户表获取对应LoginID
JOIN PatientPortalLogins Pa 
    ON IncomeSummary.PatientApplicationId = Pa.PatientApplicationId;

方法二：调整GROUP BY包含用户名（适用于一对一关联场景） #

如果PatientApplicationId与LoginID是一对一关系（即一个PatientApplicationId对应唯一的LoginID），可以直接在GROUP BY中加入Pa.LoginID，解决SQL严格模式下的字段不匹配问题：

SELECT 
    Pa.LoginID,
    SUM(CB.AFDC + CB.ChildSupport + CB.FoodStamps + CB.OtherIncome + CB.WagesSalary + CB.VeteransBenefit + CB.SocialSecurity + CB.Retirement + CB.GeneraAssistance) AS TotalIncome,
    SUM(CB.WaterSewer + CB.Transportation + CB.HouseRent + CB.FoodCost + CB.Electricity + CB.DoctorVisit + CB.ChildCare + CB.CarPayment + CB.OtherExpense) AS TotalExpenses
FROM Client_BurnOuts CB
JOIN PatientPortalLogins Pa 
    ON CB.PatientApplicationId = Pa.PatientApplicationId
GROUP BY Pa.PatientApplicationId, Pa.LoginID; -- 同时分组PatientApplicationId和LoginID

原查询问题说明 #

第一个关联查询的核心问题是：SELECT中包含Pa.LoginID，但GROUP BY仅指定了Pa.PatientApplicationId。在SQL严格模式（如MySQL的ONLY_FULL_GROUP_BY）下，这种写法会报错，因为LoginID既不是分组列，也没有被聚合函数包裹。即使在非严格模式下运行，若一个PatientApplicationId对应多个LoginID，结果也会混乱。

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