>>>
>>>CREATE TABLE Register
>>> (RecordId INT IDENTITY PRIMARY KEY NOT NULL,
>>> Sequence INT NOT NULL,
>>> Date DATETIME NOT NULL,
>>> TransType VARCHAR(5) NOT NULL,
>>> TransDesc VARCHAR(MAX) NOT NULL,
>>> Memo VARCHAR(MAX) NULL,
>>> Payment MONEY NULL,
>>> Deposit MONEY NULL,
>>> Reconciled BIT NULL)
>>>
>>>
>>>This returns all NULLs:
>>>
>>>
>>>SELECT SUM(Deposit + Payment) AS Balance FROM Register GROUP BY Sequence ORDER BY Sequence
>>>
>>>
>>>
>>
>>Change it to
>>
>>SELECT SUM(ISNULL(Deposit,0) + ISNULL(Payment,0)) AS Balance FROM Register GROUP BY Sequence ORDER BY Sequence
>>
>>Also it's not a good idea to use MONEY type.
>>
>>See
http://forums.asp.net/t/1380719.aspx>
>
>Now I'm getting "Column 'Register.RecordId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.". I need to be able to pull the balance as well as the rest of the register.
What is the query you're trying now? You need to use this one as derived table, e.g.
select Register.*, Balance from Register INNER JOIN
(SELECT Sequence, SUM(ISNULL(Deposit,0) + ISNULL(Payment,0)) AS Balance
FROM Register GROUP BY Sequence) B on Register.Sequence = B.Sequence
If it's not broken, fix it until it is.
My Blog