Re: SQL Running Total

In article <8e9ehr$qvs$1_at_nnrp1.deja.com>,   porchdog_at_my-deja.com wrote:
> I'm trying to write a query which will display a
> running total. For example, if a table has a
> day-of-the-week column, I want to display each day
> with a cumulative row total.
>
> Detail Table
> ------------
> Row Day
> --- ---
> 1 mon
> 2 mon
> 3 mon
> 4 tue
> 5 tue
> 6 wed
>
> Desired Output
> --------------
> Day Total Rows
> --- ----------
> mon 3
> tue 5
> wed 6
>
> Any ideas would be appreciated.
>
> Tom
>
Tom, I am not sure I understand your problem. As stated you should be able to do this (using data you provided):

UT1> select dayofw, count(*)
  2 from marktest2
  3 group by dayofw
  4 /

mon          3
tue          2
wed          1

3 rows selected.

But these are not the results in your example. Do you want the highest row rumber, the sum of the row number field, or the sum of the count of rows for each day of the week? If your example only showed partial data then the above may be right. If not here is the sum of row num field.

select distinct a.dayofw, CT
from marktest2 a,

      (select dayofw, sum(rown) "CT" from marktest2 group by dayofw) b where a.dayofw = b.dayofw
UT1> /

mon          5
tue          9
wed          6

3 rows selected.

This example uses an in-line view. On systems that do not support inline  views just create table b as a view and do the join on it.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.