473,216 Members | 1,351 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,216 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 5313
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.