|
|
Posted by danielbair on 07/11/06 17:50
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" <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]
|