LINQ to SQL - Join, Group, and Sum
10,562
This assumes there is only one Acct_Name per given Acct_Id, otherwise you'll get multiple records just like when you do a group by:
var query = from p in db.POOL_SIE_SUPPORTs
join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID
where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12
group p by new { p.ACCT_ID, p.ACCT_NAME } into s
select new
{
Account = s.Key.ACCT_ID,
AccountName = s.Key.ACCT_NAME,
CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)),
CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)),
YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)),
YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT))
};
Author by
psdpainter
Updated on June 04, 2022Comments
-
psdpainter almost 2 years
I have the following SQL that works as expected:
SELECT p.ACCT_ID AS [Acct], a.ACCT_NAME AS [AcctName], p.PD_NO AS [Period], p.FY_CD AS [FY], SUM(CUR_AMT) AS [CActual], SUM(CUR_BUD_AMT) AS [CBudget], SUM(YTD_AMT) AS [YActual], SUM(YTD_BUD_AMT) as [YBudget] FROM BudgetTotals p INNER JOIN BudgetAccounts a ON p.ACCT_ID = a.ACCT_ID WHERE p.ACCT_ID IN ('610','620','630','634','641','642','643','644','646','665','620','DFC','DFR','DGN','DTX') AND FY_CD == "2013" AND PD_NO == 12 AND POOL_NO == 23 GROUP BY p.ACCT_ID, a.ACCT_NAME, p.PD_NO, p.FY_CD;
I'm trying to convert the above to Linq to Sql with the following:
var query = from p in db.POOL_SIE_SUPPORTs join c in db.ACCTs on p.ACCT_ID equals c.ACCT_ID where arr.Contains(p.ACCT_ID) && p.POOL_NO == 23 && p.FY_CD == "2013" && p.PD_NO == 12 group p by p.ACCT_ID into s select new { Account = s.Key, AccountName = from acct in db.ACCTs select new { acct.ACCT_NAME }, CTDActual = string.Format("{0:C}", s.Sum(y => y.CUR_AMT)), CTDBudget = string.Format("{0:C}", s.Sum(y => y.CUR_BUD_AMT)), YTDActual = string.Format("{0:C}", s.Sum(y => y.YTD_AMT)), YTDBudget = string.Format("{0:C}", s.Sum(y => y.YTD_BUD_AMT)) };
How can I get my query to return the column
AccountName
?