Re: SQL Running Total

From: dbeg <dautbeg_at_hotmail.com>
Date: 2000/05/02
Message-ID: <8emk9j$f5o14_at_ns4.bih.net.ba>#1/1

Mark D Powell <markp7832_at_my-deja.com> wrote in message
news:8e9jct$fg$1_at_nnrp1.deja.com...

> 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 in-
> line 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.

I think that it could be what you want.
1st you need to expand yout table:
CREATE TABLE DetailTable
(id INTEGER NOT NULL PRIMARY KEY,
 dayOfWeekIdText CHAR(3) NOT NULL,
 dayOfWeekIdNumber INTEGER NOT NULL
);

INSERT INTO DetailTable  VALUES(1, 'mon', 1);
INSERT INTO DetailTable  VALUES(2, 'mon', 1);
INSERT INTO DetailTable  VALUES(3, 'mon', 1);
INSERT INTO DetailTable  VALUES(4, 'tue', 2);
INSERT INTO DetailTable  VALUES(5, 'tue', 2);
INSERT INTO DetailTable  VALUES(6, 'wed', 3);
INSERT INTO DetailTable  VALUES(7, 'thu', 4);
INSERT INTO DetailTable  VALUES(8, 'fri', 5);
INSERT INTO DetailTable  VALUES(9, 'sat', 6);
INSERT INTO DetailTable  VALUES(10, 'sun', 7);

And type next SELECT:

SELECT D1.dayOfWeekIdNumber, D1.dayOfWeekIdText, COUNT(*), COUNT(DISTINCT D1.id)

   FROM DetailTable AS D1, DetailTable AS D2

      WHERE D1.dayOfWeekIdNumber >= D2.dayOfWeekIdNumber GROUP BY D1.dayOfWeekIdNumber, D1.dayOfWeekIdText ORDER BY D1.dayOfWeekIdNumber;

Result is very similar what you want. My SQL (Mimer 8.13) rejected when I immediately put

SELECT D1.dayOfWeekIdNumber, D1.dayOfWeekIdText, COUNT(*)/COUNT(DISTINCT D1.id)

maybe you will be better luck.

I am sory for my english (my SQL is better then my english!?)

Dzavid Dautbegovic
Bosnia and Herzegovina
dautbeg_at_hotmail.com Received on Tue May 02 2000 - 00:00:00 CEST