A Bug With Running Total Query?

4 Jun 2005 9:28 AM

Nathan

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

4 Jun 2005 1:53 PM

John Bell

Hi

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/newsgroups/default.asp?icp=sqlserver2005&slcid=us&newsgroup=microsoft.private.sqlserver2005.relationalserver.tsql

John

Show quoteHide quote

"Nathan" <Nat***@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

4 Jun 2005 2:47 PM

Steve Kass

Nathan wrote:

Show quoteHide quote

>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

Show quoteHide quote

>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
>
>

4 Jun 2005 6:32 PM

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

Show quoteHide quote

"Steve Kass" wrote:

>
>
> 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.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
> >
> >
>