Re: How to make sum of columns in 1 row be starting point for next row

From: Andy Hassall <andy_at_andyh.org>
Date: Sun, 12 Jan 2003 23:07:42 +0000
Message-ID: <04t32vsieiltrfcre5tgd1pcvpkgsnnpnm@4ax.com>

On 11 Jan 2003 14:35:36 -0600, tyutuyt_at_gfd.com (sfdsf) wrote:

>We have sales data for products on a year basis for a 5 year forecast. For
>year 1 we add the base price + shipping + tax to get the total price. that
>total price then becomes the base price for the next year. so for product
>A1 we have the following
>
>name base_cost shipping tax total year
>
>A1 10 0 3 15 1
>A1 15 2 1 18 2
>A1 18 1 6 26 3
>etc
>
>So to report the forecast we just select the above rows displaying them for
>each year (in fact the table was premade for just that purpose (reason was
>first intital base cost was very complex and time comsuming to calculate.)
>
>Problem is that they want to see a forecast minus the impact of shipping
>costs. What will it be if the shipping cost is zero for all years? Of course
>that is not how the data is in the table. I can't just do a select like in
>the first one. All the data is in the table, problem is how to select it
>right.
>
>The total for Row 1 needs to be the base cost for row 2 and so on..
>
>Any ideas on how this can be done without doing it in stored procedure ( user
>function or analytic function would be great)? this is going to be used in a
>report program that can not call stored procedures. I am at wits end!

You can do a running total with analytic functions, a simple example being:

SQL> select * from sales;

YEAR AMOUNT
---------- ----------

         1          3
         2          4
         3          5

SQL> select year,
  2         amount,
  3         sum(amount) over (order by year rows unbounded preceding)

running_total
4 from sales
5 order by year;

YEAR AMOUNT RUNNING_TOTAL
---------- ---------- -------------

         1          3             3
         2          4             7
         3          5            12

Just alter the expression in the sum() to include or exclude whatever fields you want.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space | disk usage analysis tool

Received on Sun Jan 12 2003 - 17:07:42 CST