|
|
Posted by Jack Vamvas on 07/11/06 16:05
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" <bair.daniel@gmail.com> wrote in message
news:1152632045.314756.24750@h48g2000cwc.googlegroups.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
>
Navigation:
[Reply to this message]
|