title: LINQ to SQL - Join, Group, and Sum
body: <p>I have the following SQL that works as expected: </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I'm trying to convert the above to Linq to Sql with the following: </p>
  
  <pre><code>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) &amp;&amp; p.POOL_NO == 23 &amp;&amp; p.FY_CD == "2013" &amp;&amp; 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 =&gt; y.CUR_AMT)),
                  CTDBudget = string.Format("{0:C}", s.Sum(y =&gt; y.CUR_BUD_AMT)),
                  YTDActual = string.Format("{0:C}", s.Sum(y =&gt; y.YTD_AMT)),
                  YTDBudget = string.Format("{0:C}", s.Sum(y =&gt; y.YTD_BUD_AMT))
             };
  </code></pre>
  
  <p>How can I get my query to return the column <code>AccountName</code>?</p>