title: SQL Update multiple values with a group by statement not working
body: <p>I have tried checking similar posts but just cannot get this thing right, please help.</p>
  
  <p>This query returns the correct data but the update statement complains because it says more than </p>
  
  <pre><code>SELECT 
      classificationchartofaccountname, 
      -SUM(ISNULL(HLD.Nominal, 0))  AS 'Total' 
  FROM
      [dbsFPM].[dbo].[rpt_Holdings] HLD 
  INNER JOIN 
      [dbsFPM].[dbo].[rpt_FundAccounts] FA ON HLD.Username = FA.Username 
                                           AND HLD.ClientPortfolioCode = FA.ClientPortfolioCode 
                                           AND FA.ClassificationChartOfAccountName = HLD.InstrumentName 
  WHERE 
      HLD.username =  @username 
      AND FA.Username = @username 
      AND FA.Narration = 'IncomeStatement' 
      AND FA.AccountType = 'Expense'  
  GROUP BY 
      ClassificationChartOfAccountName)
  </code></pre>
  
  <p>RETURNS:</p>
  
  <pre><code>classificationchartofaccountname    Total
  
  General Expenses on ZAR 17578.38
  
  lc Audit 2014 on ZAR    6533.12
  
  lc Trustee Fees - Other on ZAR  840.41
  
  lc Trustee Fees-ZAR 38524.34
  
  Management Fees-ZAR 6452680.040
  
  Performance Fees-ZAR    175.80
  </code></pre>
  
  <p>The update statement i want to use errors out as follows: </p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Line 1<br>
    Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression.</p>
  </blockquote>
  
  <p>Here is the update:</p>
  
  <pre><code>UPDATE [rpt_FundAccounts]
  SET ReconNarration = 'ExpenseAnalysis_Period', 
      Recon = (SELECT -SUM(ISNULL(HLD.Nominal, 0)) 
               FROM [dbo].[rpt_Holdings] HLD 
               INNER JOIN [dbo].[rpt_FundAccounts] FA ON HLD.Username = FA.Username 
                                                      AND HLD.ClientPortfolioCode = FA.ClientPortfolioCode 
                                                      AND FA.ClassificationChartOfAccountName = HLD.InstrumentName 
               WHERE HLD.username =  @Username 
                 AND FA.Username = @Username 
                 AND FA.Narration = 'IncomeStatement' 
                 AND FA.AccountType = 'Expense' 
                 /* and ClassificationChartOfAccountName = 'Management Fees-ZAR'*/ 
               GROUP BY ClassificationChartOfAccountName)
  WHERE 
      username = @username  
      AND Narration = 'IncomeStatement' 
      AND AccountType = 'Expense' 
      /*and ClassificationChartOfAccountName = 'Management Fees-ZAR' */
  </code></pre>
  
  <p>The issue is i want to update all the <code>ClassificationChartOfAccountNames</code> with their respective totals from <code>[rpt_Holdings]</code></p>
  
  <p>Please help, sorry I am still a beginner!</p>
  
  <pre><code>ClassificationChartOfAccountName
  
  Bank Charge Fees on GBP
  Bank_Interest Charges on ZAR
  Distribution Paid
  Expenses
  Foreign Withholding Tax
  General Expenses on EUR
  General Expenses on USD
  General Expenses on ZAR
  lc Audit 2012 on ZAR
  lc Audit 2013 Final on ZAR
  lc Audit 2013 on ZAR
  lc Audit 2014 on ZAR
  lc Scrip - SBSA on ZAR
  lc Trustee Fees - Other on ZAR
  lc Trustee Fees-ZAR
  Management Fees-ZAR
  Performance Fees-ZAR
  Realised Foreign Exchange Gain
  Realised GainLoss
  Realised Pull To Par
  </code></pre>