SQLTeam.com | Weblogs | Forums

Dived a sum field


#1

Hi

Im trying to divided the sum of the total column by sum of the same column when account numbers match up.

i have the following sql script done

UPDATE m
SET m.AccruedInterest = f.UNITS
FROM dbo.NTWorkingDataloadFile1 m
INNER JOIN
(
  SELECT Account_Number, UNITS/(SELECT SUM(UNITS)FROM dbo.NTMercerTransctionHoldingsTable) as UNITS
  FROM dbo.NTMercerTransctionHoldingsTable
  WHERE Account_Number=Account_Number
  GROUP BY  Account_Number
) f ON m.MasterIdentifier = f.Account_Number

but getting the follwoinf error

Msg 8120, Level 16, State 1, Line 7
Column 'dbo.NTMercerTransctionHoldingsTable.Units' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

any ideas. i didnt think i had to put the units in the group clause. but when i do i get this error


#2

You don't need GROUP BY at all (there is no aggregate function in that sub-select, only in the

SELECT SUM(UNITS)FROM dbo.NTMercerTransctionHoldingsTable

bit)