A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>