我有兩張桌子。一個是userInfo表，另一個是income expenses表，這個表有多個列。我想對這些列進行求和，并GROUP BY嘗試顯示它。TotalIncomeTotalexpensesuserName
這是查詢：
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
我只能得到不同使用的總和：
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
uj5u.com熱心網友回復：
嘗試使用子查詢：
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;
uj5u.com熱心網友回復：
首先從 Client_BurnOuts 聚合按 PatientApplicationId 分組的值，然后加入用戶表以獲取用戶詳細資訊。請嘗試以下查詢。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/510543.html
標籤：C＃sqlsql服务器sql-server-2019-express
下一篇：對不同行的2列求和