cumulative DSum

[scubadiver]

I have a group totals query with the following

Period (group by)
Week (group by)
Dept (group by)
Hours (Sum)
Absence (sum)

I want to create a cumulative sum for the "hours" and "absence" grouped by
"period", "week" and "dept". Here is the SQL.


SELECT tble_week.Period, tble_hrs.RecWk, tble_hrs.Dept,
Sum(tble_hrs.HrsWrk_basic) AS Hours, Sum(tble_hrs.AbsTot) AS Absence
FROM tble_week INNER JOIN tble_hrs ON tble_week.RecWk = tble_hrs.RecWk
GROUP BY tble_week.Period, tble_hrs.RecWk, tble_hrs.Dept
HAVING (((tble_hrs.Dept)<>"Operations"))
ORDER BY tble_week.Period, tble_hrs.RecWk;

Cheers.

 

[Marshall Barton]

I have a group totals query with the following

Period (group by)
Week (group by)
Dept (group by)
Hours (Sum)
Absence (sum)

I want to create a cumulative sum for the "hours" and "absence" grouped by
"period", "week" and "dept". Here is the SQL.

SELECT tble_week.Period, tble_hrs.RecWk, tble_hrs.Dept,
Sum(tble_hrs.HrsWrk_basic) AS Hours, Sum(tble_hrs.AbsTot) AS Absence
FROM tble_week INNER JOIN tble_hrs ON tble_week.RecWk = tble_hrs.RecWk
GROUP BY tble_week.Period, tble_hrs.RecWk, tble_hrs.Dept
HAVING (((tble_hrs.Dept)<>"Operations"))
ORDER BY tble_week.Period, tble_hrs.RecWk;

Is this what you want?

SELECT tble_week.Period,
tble_hrs.RecWk,
tble_hrs.Dept,
Sum(tble_hrs.HrsWrk_basic) AS Hours,
Sum(tble_hrs.AbsTot) AS absence,
Hours + absence AS total
FROM . . .

 

[scubadiver]

Er, no. This is what I want:

Period RecWk Dept Hours Totals
7 06/07/2007 Administration 945.75 945.75
7 13/07/2007 Administration 934.25 1880.00
7 20/07/2007 Administration 946.00 2826.00
7 27/07/2007 Administration 854.00 3680.00

"totals" is a cumulative sum (as it says in the title) but I want to the sum
to start again for each new period and department (it has occurred to me that
week won't be required).

 

[Marshall Barton]

Er, no. This is what I want:

Period RecWk Dept Hours Totals
7 06/07/2007 Administration 945.75 945.75
7 13/07/2007 Administration 934.25 1880.00
7 20/07/2007 Administration 946.00 2826.00
7 27/07/2007 Administration 854.00 3680.00

"totals" is a cumulative sum (as it says in the title) but I want to the sum
to start again for each new period and department (it has occurred to me that
week won't be required).

Ahh, I see now. Most people call that a "running sum".

Let's say your current query is named HRSxWK, then you can
sum the sums this way:

SELECT Period, RecWk, Dept, Hours,
(SELECT Sum(X.Hours)
FROM HRSxWK As X
WHERE X.Period = HRSxWK.Period
And X.Dept = HRSxWK.Dept
Ans X.RecWk <= HRSxWK.RecWk) As Totals
FROM HRSxWK
ORDER BY Period, DeptX, RecWk

 

[scubadiver]

cheers

--
www.ae911truth.org

Ahh, I see now. Most people call that a "running sum".

Let's say your current query is named HRSxWK, then you can
sum the sums this way:

SELECT Period, RecWk, Dept, Hours,
(SELECT Sum(X.Hours)
FROM HRSxWK As X
WHERE X.Period = HRSxWK.Period
And X.Dept = HRSxWK.Dept
Ans X.RecWk <= HRSxWK.RecWk) As Totals
FROM HRSxWK
ORDER BY Period, DeptX, RecWk