CODEWITHSUNDEEP
sqlms-accesssumsubquerycumulative-sum
ColorfulWind
ColorfulWind

Reputation: 87

Finding running sum in SQL (Access 07-10)

I am trying to find a running sum. When I run this I get each time the account made a purchase within the two week span (not the sum). The question i'm trying to answer is, how much is each account spending within the first two weeks.

SELECT 
    dbo_AAS.Account, 
    dbo_AM.ED, 
    ([dbo_AM].[ED]+14) AS TwoWeeks, 
    dbo_AAS.RD, 
    Sum(Abs([dbo_AAS].[WA])) AS ABSWA    
FROM dbo_AAS 
INNER JOIN dbo_AM 
    ON dbo_AAS.Account = dbo_AM.Account
GROUP BY 
    dbo_AAS.Account, 
    dbo_AM.ED, 
    dbo_AAS.RD
HAVING ((
    (dbo_AM.ED) Is Not Null) 
    AND ((dbo_AAS.RD) Between [dbo_AM].[ED] And [dbo_AM].[ED]+14));

The Results:

+-------+----------+----------+----------+-----+
|Account|ED        |TwoWeeks  |RD        |ABSWA|
+-------+----------+----------+----------+-----+
|34     |11/23/2013|12/07/2013|11/23/2013|18   |
+-------+----------+----------+----------+-----+
|34     |11/23/2013|12/07/2013|11/27/2013|30   |
+-------+----------+----------+----------+-----+
|34     |11/23/2013|12/07/2013|12/02/2013|15   |
+-------+----------+----------+----------+-----+
|34     |11/23/2013|12/07/2013|12/05/2013|20   |
+-------+----------+----------+----------+-----+

Desired Results

+-------+----------+----------+--+-----+
|Account|ED        |TwoWeeks  |RD|ABSWA|
+-------+----------+----------+--+-----+
|34     |11/23/2013|12/07/2013|* |83   |
+-------+----------+----------+--+-----+

Upvotes: 1

Views: 92

Answers (3)

ColorfulWind
ColorfulWind

Reputation: 87

A sub query did the trick. thank you for your guys help.

SELECT [%$##@_Alias].account, dbo_AM.FN, dbo_AM.LN, Sum([%$##@_Alias].abswa) AS WA, dbo_AM.ED, ([dbo_am].[ED]+14) AS TwoWeeks FROM (SELECT dbo_AAS.Account, dbo_AM.ED, dbo_AAS.RD, Sum((Abs([dbo_AAS].[WS]))) AS ABSWA

FROM dbo_AAS INNER JOIN dbo_AM ON dbo_AAS.Account = dbo_AM.Account

GROUP BY dbo_AAS.Account, dbo_AM.ED, dbo_AAS.RD

HAVING (((dbo_AAS.RD) Between [dbo_AM].[ED] And [dbo_AM].[ED]+14)) ) AS [%$##@_Alias] INNER JOIN dbo_AM ON [%$##@_Alias].Account = dbo_AM.Account GROUP BY [%$##@_Alias].account, dbo_AM.FN, dbo_AM.LN, dbo_AM.ED, ([dbo_am].[ED]+14);

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT dbo_AAS.Account,
       dbo_AM.ED,
       ([dbo_AM].[ED]+14) AS TwoWeeks,
       Sum(Abs([dbo_AAS].[WA])) AS ABSWA
FROM dbo_AAS
INNER JOIN dbo_AM
   ON dbo_AAS.Account = dbo_AM.Account
WHERE dbo_AAS.RD BETWEEN '11/23/2013' AND '12/05/2013'
GROUP BY dbo_AAS.Account, dbo_AM.ED
HAVING (((dbo_AM.ED) Is Not Null) AND
       ((dbo_AAS.RD) Between [dbo_AM].[ED] And [dbo_AM].[ED]+14));

Just remove RD altogether.

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8404

You're grouping by RD, so that's going to give you one line for each value in RD. Take that field out of the GROUP BY statement and you should get your answer. You'll also have to take it out of the SELECT statement or it will throw an error at you.

Upvotes: 1

Related Questions