Re: SQL Running Total

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

dbeg <dautbeg_at_hotmail.com> wrote in message

news:8emk9j$f5o14_at_ns4.bih.net.ba...


> 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

>
> 2nd & 3rd solution

I have also found two better solution:
If you fill id strictly ascending without any hole, you do not need to expand your table and you can try:

SELECT dayOfWeek, Max(id) AS totalRows

   FROM DetailTable
GROUP BY dayOfWeek
ORDER BY totalRows;

or the best solution not depending of way filling id is:

SELECT D1.dayOfWeek, COUNT(DISTINCT D2.id) AS totalRows

   FROM DetailTable AS D1, DetailTable AS D2

      WHERE D1.id >= D2.id
GROUP BY D1.dayOfWeek
ORDER BY totalRows; Received on Tue May 02 2000 - 00:00:00 CEST