Posted by Hugo Kornelis on 07/11/06 20:34

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

[Back to original message]