Find UserName in Different Table Using UserId-CodePudding

I have two tables. One is the userInfo table another is the income expenses table, This table has multiple columns. I want to summation these columns using GROUP BY and try to show TotalIncome, Totalexpenses and userName that it.

Here is the query:

    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

I can get only the total summation different used:

    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

CodePudding user response：

Try using a subquery:

select Pa.LoginID, TotalIncome , TotalExpences
from
    (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) AS cb
Join PatientPortalLogins Pa on CB.PatientApplicationId = Pa.PatientApplicationId

CodePudding user response：

First aggregate the values grouped by PatientApplicationId from Client_BurnOuts and then join with user table to get user details. Please try the below query.

SELECT Pa.PatientApplicationId, Pa.UserName, Summary.TotalIncome, Summary.TotalExpences
    (
    SELECT CB.PatientApplicationId ,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 
    GROUP BY CB.PatientApplicationId
) AS Summary            
INNER JOIN PatientPortalLogins Pa ON Summary.PatientApplicationId = Pa.PatientApplicationId