Find UserName in Different Table Using UserId

Question

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

Answer 1

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.PatientApplicationIdl;

Answer 2

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