Re: SQL Running Total

>> I'm trying to write a query which will display a running total. <<

No, you are asking for a running tally. You can not do a summation on a day of the week or other token. Ever hear the old joke about the teachers asking the class "What 3 time 8?" and Billy answers "Thursday!" "Wrong!", Marvin answers "Red!", "Wrong!" Sally answers "24!", and rthe tacher replies "That's right! Now tell the class how you got that answer" Sally replies "I divided Red by Thursday!"

>> For example, if a table has a day-of-the-week column, I want to
display each day with a cumulative row total. <<

It would help if you would post DDL instead of some personal pseudocode.   But your example stinks. Days of the week are a cycle, not a sequence. Mondays come before mondays and after Mondays.

I also hope that the attribute named "row" is an unfortunate coincidence and that you are not putting the physical storage in the table as an attribute.

Let's try again, with some DDL

CREATE TABLE Foobar
(fleem_date DATE NOT NULL PRIMARY KEY,
 fleem CHAR(3) NOT NULL);

INSERT INTO Foobar VALUES ('2000-01-01', 'mon');
INSERT INTO Foobar VALUES ('2000-01-02', 'mon');
INSERT INTO Foobar VALUES ('2000-01-03', 'mon');
INSERT INTO Foobar VALUES ('2000-01-04', 'mon');
INSERT INTO Foobar VALUES ('2000-01-05', 'tue');
INSERT INTO Foobar VALUES ('2000-01-06', 'tue');
INSERT INTO Foobar VALUES ('2000-01-07', 'wed');

I think this will get the results you wanted:

SELECT F1.fleem, COUNT(F2.fleem) AS tally   FROM Foobar AS F1, Foobar AS F2
 WHERE F1.fleem_date

• (SELECT MAX(fleem_date) FROM Foobar AS F3 WHERE F1.fleem = F3.fleem) AND F1.fleem_date >= F2.fleem_date;

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CEST