Rolling Sum, Help!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a question about a query I'm trying to help a co-worker write.

I'll make the scenario as simple as possible so we'll use a single example table with two columns

item valu
-----------------------
1 6
2 1
3 1
4
5 5
6 5
7
8

Here we have two columns: an item number and a value. The total sum of the values in this example is 100. I need to write a query to return those records that make up 80% of the total VALUE of the records in the table. So, in this example, I would expect a result set with 3 records (items 1, 2 and 3)

I've been able to do this programmatically with ASP (vbscript, by looping through the recordset and calculating a rolling sum to compare to the total sum) and I've also been able to do this in SQL Server (using a cursor to walk through each of the records and calculate a rolling sum, similar to doing it in ASP for example)

So far I've had no luck at all with Access, as it doesn't seem to support a mechanism to calculate a rolling sum as we traverse the "recordset". Access doesn't seem to support the cursor syntax I used in SQL Server either

Any ideas

TIA - Tom
 
Hi,


SELECT a.Item. LAST(a.Value), SUM(b.value)
FROM myTable As a INNER JOIN myTable As b ON a.item >= b.item
GROUP BY a.Item
HAVING SUM(b.value) <= 0.80 * (SELECT SUM(value) FROM myTable)



.... see, see ? no recordset. Here, you can replace LAST with FIRST, MIN,
MAX, SUM or AVG, it does not matter since it operates on a single value
(repeated many times, but always the same), anyhow. Alternatively, you can
remove it, but add a.Value in the GROUP BY.


Hoping it may help,
Vanderghast, Access MVP



Tom said:
I have a question about a query I'm trying to help a co-worker write.

I'll make the scenario as simple as possible so we'll use a single example table with two columns.

item value
------------------------
1 60
2 10
3 10
4 5
5 5
6 5
7 4
8 1

Here we have two columns: an item number and a value. The total sum of the
Click to expand...
values in this example is 100. I need to write a query to return those
records that make up 80% of the total VALUE of the records in the table. So,
in this example, I would expect a result set with 3 records (items 1, 2 and
3).
I've been able to do this programmatically with ASP (vbscript, by looping
Click to expand...
through the recordset and calculating a rolling sum to compare to the total
sum) and I've also been able to do this in SQL Server (using a cursor to
walk through each of the records and calculate a rolling sum, similar to
doing it in ASP for example).
So far I've had no luck at all with Access, as it doesn't seem to support
Click to expand...
a mechanism to calculate a rolling sum as we traverse the "recordset".
Access doesn't seem to support the cursor syntax I used in SQL Server
either.
 
Thanks for the help Vanderghast, but I'm not using a GROUP BY clause. I'm not sure I understand it's use here...
 
Hi,


We first have to take a look at the inner join, to understand, since it is
the master of the trick, not the Group By.


SELECT a.*, b.*
FROM myTable As a INNER JOIN myTable As b ON a.item >= b.item

we got: ( I add the ---------- lines to delimit the future groups)


a.item a.value b.item b.value
----------------------------------------------
1 60 1 60
---------------------------------------------
2 10 1 60
2 10 2 10
---------------------------------------------
3 10 1 60
3 10 2 10
3 10 3 10
--------------------------------------------
4 5 1 60
4 5 2 10
4 5 3 10
4 5 4 5
-------------------------------------------
5 5 1 60
etc.



Now, if we GROUP by a.item, that is, we take just one record for a given
value of a.item, what do we do with the other ones. Let take we use
LAST(a.value) and SUM(b.value)

a.item MAX(a.value) SUM(b.value)
1 60 60
2 10 70
3 10 80
4 5 85
5 5 90
6 5 95
7 4 99
8 1 100


where the MAX and SUM occur on the previous result of our JOIN, for the
given "group".

The HAVING clause occur after the grouping, we filter in those HAVING
SUM(b.Value) <= 0.8*100 (the sub select evaluates to 100, here), so the
result:

a.item MAX(a.value) SUM(b.value)
1 60 60
2 10 70
3 10 80



Hoping it may help,
Vanderghast, Access MVP



Tom said:
Thanks for the help Vanderghast, but I'm not using a GROUP BY clause. I'm
Click to expand...
not sure I understand it's use here...
 
Hi,


And note that if the Item where not increasing as the value is decreasing,
we would change the inner join to:

FROM myTable As a INNER JOIN myTable As b
ON ( a.value > b.value OR (a.value=b.value AND a.pk>=b.pk) )


and then, definitively, we would GROUP BY on a.pk (pk = primary key):


SELECT a.pk, LAST(a.value), SUM(b.value)
FROM myTable As a INNER JOIN myTable As b
ON ( a.value > b.value OR (a.value=b.value AND a.pk>=b.pk) )
GROUP BY a.pk
HAVING SUM(b.value) <= 0.80*(SELECT SUM(value) FROM myTable)



Vanderghast, Access MVP
 
Thanks for your very descriptive help... It's definately setting us in the right direction...

I was able to get the appropriate results when I created the same sample table I used in my initial post. However, after trying to implement this with the actual table I'm using, I think I may have made my example table a little too simple... What if the table were like so?

item_no value
-------------------------
H40JK 60
R302I 10
KF34K 10

And so on... I've made these item_no's up but they more accurately represent the data I'm working with.. :|
 
Hi,



If you have a primary key, pk, then ( I used the wrong inequation, >
instead of <, in my previous post)

SELECT a.pk, LAST(a.value), SUM(b.value)
FROM myTable As a INNER JOIN myTable As b
ON ( a.value < b.value OR (a.value=b.value AND a.pk<=b.pk) )
GROUP BY a.pk
HAVING SUM(b.value) <= 0.80*(SELECT SUM(value) FROM myTable)


else, assuming that the couple (item_no, value) is unique:

SELECT a.item_no, a.value, SUM(b.value)
FROM myTable As a INNER JOIN myTable As b
ON ( a.value < b.value OR (a.value=b.value AND a.item_no<=b.item_no) )
GROUP BY a.value, a.item_no
HAVING SUM(b.value) <= 0.80*(SELECT SUM(value) FROM myTable)
ORDER BY a.item_no, a.value DESC;




Hoping it may help,
Vanderghast, Access MVP



Tom said:
Thanks for your very descriptive help... It's definately setting us in the right direction...

I was able to get the appropriate results when I created the same sample
Click to expand...
table I used in my initial post. However, after trying to implement this
with the actual table I'm using, I think I may have made my example table a
little too simple... What if the table were like so?
item_no value
-------------------------
H40JK 60
R302I 10
KF34K 10

And so on... I've made these item_no's up but they more accurately
Click to expand...
represent the data I'm working with.. :|
 
Thanks again Michel, awesome verbose helpful descriptive responses! Appreciate you taking the time to help.
 
Back
Top