Transact-SQL SUM Help!

SQL Apprentice Question
I am trying to tally up information from two tables, but I am running
in to trouble and I can't seem to figure this out. I have a
CreditsTable and a DebitsTable and I am trying to create a querry that
sums up the info from each per UserID and returns TotalCredits,
TotalDebits, and a Ballance.

CreditsTable 
UserID  Ammount Applied 
+----------+----------+----------+ 
 |  192    |  1       |  True   | 
 |  192    |  2       |  True   | 
 |  207    |  1       |  True   | 
 |  207    |  1       |  True   | 
 |  207    |  2       |  True   | 
 |  212    |  3       |  True   | 


DebitsTable 
UserID  Ammount Applied 
+----------+----------+----------+ 
 |  192    |  1       |  True   | 
 |  207    |  1       |  True   | 
 |  207    |  1       |  True   | 

***This is the Function I have tried, but it doesn't return the correct
results


ALTER FUNCTION [dbo].[BallanceTotals]()
RETURNS TABLE
AS
RETURN
(
SELECT DISTINCT
dbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,
SUM(dbo.DebitsTable.Ammount) AS TotalDebits,
SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS Ballance
FROM
dbo.CreditsTable FULL OUTER JOIN
dbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserID
WHERE
(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)
GROUP BY
dbo.CreditsTable.UserID
)


*** This is what it returns, it is not adding things up correctly (it
looks like it is adding NULL values as 1 instead of 0 or something)

BallanceTotals 
              Total     Total 
UserID    Credits  Debits  Ballance 
+----------+----------+----------+----------+ 
 |  192    |  3       |  2        |  1       | 
 |  207    |  4       |  3        |  1       | 
 |  212    |  3       |            |           | 


*** This is what I want it to return! 


BallanceTotals 
              Total     Total 
UserID    Credits  Debits  Ballance 
+----------+----------+----------+----------+ 
 |  192    |  3       |  1        |  2       | 
 |  207    |  4       |  2        |  2       | 
 |  212    |  3       |  0        |  3       | 

I would really appreciate some help in getting this to work correctly!



Celko Answers

>>. I have a CreditsTable and a DebitsTable and I am trying to create a querry that sums up the info from each per UserID and returns TotalCredits, TotalDebits, and a Ballance. <<


This design flaw is called attribute splitting. You need a single
table, say Accounts, that shows events related to each account. Also,
we do not have BOOLEAN data types in SQL. It would be much more
meaningful to show the time that a posting was made to the account.

CREATE TABLE Accounts
(user_id INTEGER NOT NULL,
posting_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
posting_amt DECIMAL (12,4) NOT NULL
CHECK (posting_amt <> 0.00));


We have negative numbers and do not have to use credit/debit columns as
they did in Roman times.