473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 5317
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Demetris | last post by:
Hello there. I have ORACLE 8.1.7.4 server on AIX 5.1. I have another server (BIZTALK) with Oracle client 8.1.7. The system is running ok. I can connect from the client on my database server just...
2
by: pierig.gueguen | last post by:
Hello, I encounter a problem with a small portion of sqlcode. I try to go on database using "use dbname" but i always stay in master. I execute script with the sa user. declare...
2
by: Mario L. Eppinger | last post by:
Hi @ll, I have a problem using transactions in c#: I have a class that implements all my database actions. In this class I have -) The SqlConnection conn (which is opened in the ctor) -) Some...
0
by: perspolis | last post by:
Hi all I used SqlTransaction inmy application.. SqlTransaction transact=sqlConnection1.BeginTransaction(IsoLationLevel.Something); sqlSelect.Transaction=transact; sqlInsert.Transaction=transact;...
3
by: Tina | last post by:
I have been trying to prepare for 70-305 using the Preplogic CD that came with the QUE book by Mike Gunderloy. This test has wrong answers. For instance... PrepLogic question #39 asks what...
0
by: et_ck | last post by:
Hi, We have a web services running to facilitate our clients in performing transactions. Below is the sample message flow: 1) App -> WS WS - perform transact WS - send response 2)
11
by: moondaddy | last post by:
I have a .net 2.0 smarclient app and am trying to deploy it to IIS where users can access it from. I created an application folder in IIS where I'm trying to deployee to. 1) When the...
2
by: Barry | last post by:
After reading Andrei Alexandrescu and Petru Marginean's "Generic: Change the Way You Write Exception-Safe Code ¡ª Forever" http://www.ddj.com/cpp/184403758 I borrow Boost.Function, which makes...
3
by: Scott Bradley | last post by:
Hi All, We are using SQL2000 servers with transact replication to a warm stand-by server. We are seeing the following error from the Log reader agents. Does anyone have any insight on this...
62
by: sbandalli | last post by:
Hello All, I have a list of items in the listbox.(The items to the list box were added from the textbox eg ( listBox1.Items.Add(textBox1.Text)), Now If I have to alter the Listbox eg: If...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.