469,148 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Transact-SQL SUM Help!

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!

-Daniel

Jul 11 '06 #1
5 5231
Hi

For the NULL use ISNULL(colName,0) , check BOL, in other words if there is a
NULL it will default to 0, and therefore you can ncalculate.
For the SUM problem, do something like -
totalDebits = (SELECT SUM(dbo.DebitsTable.Ammount) FROM DebitsTable where
userID =myAlias.DebitsTable.userID )
give the tables you are referencing an alias , so you can reference them in
other parts of the query.

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"danielbair" <ba*********@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
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!

-Daniel

Jul 11 '06 #2
I'm not really going to get into the schema design. I'll just assume
that you left a lot out for the post. You can do this much easier if
you have a single table to handle your debits and credits...anyway...

BEGIN TRANSACTION

DECLARE @tCredits TABLE (TransactionID INT IDENTITY(1,1), UserID INT,
Amount MONEY, Applied BIT)
DECLARE @tDebits TABLE (TransactionID INT IDENTITY(1,1), UserID INT,
Amount MONEY, Applied BIT)
DECLARE @tUsers TABLE (UserID INT)

INSERT INTO @tUsers
SELECT 192 UNION ALL
SELECT 207 UNION ALL
SELECT 212

INSERT INTO @tCredits
SELECT 192, 1, 1 UNION ALL
SELECT 192, 2, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 2, 1 UNION ALL
SELECT 212, 3, 1
INSERT INTO @tDebits
SELECT 192, 1, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 1, 1

--SELECT * FROM @tUsers
--SELECT * FROM @tCredits
--SELECT * FROM @tDebits

SELECT UserID,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tCredits WHERE UserID =
U.UserID) TotalCredits,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tDebits WHERE UserID =
U.UserID) TotalDebits,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tCredits WHERE UserID =
U.UserID)-(SELECT ISNULL(SUM(Amount), 0.0) FROM @tDebits WHERE UserID =
U.UserID) Balance
FROM @tUsers U

ROLLBACK TRANSACTION

Jul 11 '06 #3
Thank you very much! I was thinking of ISNULL but couldn't figure out
how to implement it. It is working now!

-Daniel

Jack Vamvas wrote:
Hi

For the NULL use ISNULL(colName,0) , check BOL, in other words if there is a
NULL it will default to 0, and therefore you can ncalculate.
For the SUM problem, do something like -
totalDebits = (SELECT SUM(dbo.DebitsTable.Ammount) FROM DebitsTable where
userID =myAlias.DebitsTable.userID )
give the tables you are referencing an alias , so you can reference them in
other parts of the query.

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"danielbair" <ba*********@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
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!

-Daniel
Jul 11 '06 #4
>>. 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.

Jul 11 '06 #5
On 11 Jul 2006 08:34:05 -0700, danielbair wrote:
>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.
(snip)
>***This is the Function I have tried, but it doesn't return the correct
results
(snip)

Hi Daniel,

That's because you join rows from two tables on only UserID - so if a
user has seven credits and four debits, each of the credits gets joined
to each of the credits for a total of 28 rows - and all SUM results will
be less than useless.

The answer is to use two derived tables with pre-aggregated data and
join them:

SELECT COALESCE(c.UserID, d.UserID) AS UserID,
c.Total AS TotalCredits,
d.Total AS TotalDebits,
c.Total - d.Total AS Balance
FROM (SELECT UserID, SUM(Amount) AS Total
FROM dbo.CreditsTable
WHERE Applied = 1
GROUP BY UserID) AS c
FULL OUTER JOIN (SELECT UserID, SUM(Amount) AS Total
FROM dbo.DebitsTable
WHERE Applied = 1
GROUP BY UserID) AS d
ON d.UserID = c.UserID

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Of course, the best solution is to switch to a single table model (as
suggested by various other posters).

--
Hugo Kornelis, SQL Server MVP
Jul 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Demetris | last post: by
2 posts views Thread by Mario L. Eppinger | last post: by
reply views Thread by perspolis | last post: by
3 posts views Thread by Tina | last post: by
2 posts views Thread by Barry | last post: by
3 posts views Thread by Scott Bradley | last post: by
62 posts views Thread by sbandalli | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.