Re: Getting running totals

This is just psuedo code but what about this approach...
SELECT ,
CASE WHEN MONTH='JANUARY' THEN 1
WHEN MONTH='OCTOBER THEN 10
ELSE 999999
END
FROM test;
Then order by the value of the case statement to get the months in the
correct order.

On Apr 8, 2005 10:40 AM, Rodolfo J. Paiz <rpaiz(at)simpaticus(dot)com> wrote:
>
> On Thu, 2005-04-07 at 22:00 -0500, David wrote:
> > This may be another elementary question - I found some hints in the
> > archives that may have answered my question, but not sure..
> >
>
> That's OK... I just had another elementary problem similar to yours
> (been using SQL for less than a month or so), so allow me to share the
> solutions or workarounds that I used.
>
> > I'm designing a database to maintain business records. I'd like to
> > get an output like so:
> >
> > Month | Month Ttl | Year-to-Date
> > -------+-----------+--------------
> > Jan | 25.00 | 25.00
> > Feb | 25.00 | 50.00
> > Mar | 50.00 | 100.00
> > Apr | 50.00 | 150.00
> > (4 rows)
> >
>
> Since you have the query you want, but are concerned about ordering,
> what I did in order to find a simple solution was to include the year in
> order to avoid ambiguity and use the format "YYYY-MM" to achieve this
> end. YYYY-MM will sort correctly whether it's numeric or character.
> Done. :-)
>
> The other approach I considered, but did not use, took into account the
> fact that I'm putting the results of these queries into HTML tables in
> web pages and using PHP to make my code-writing easier. That other
> approach was to add the two-digit month to the query as the first
> column, sort by that column, and then in the web page code simply omit
> the display of your first column. Thus, the date displayed is the month
> in character form but the sort was done on the numeric. If your front-
> end display format has that option, such a strategy will also work.
>
> > Now - about the Year-to-Date column.
> >
> > From my experimentation and research that I've attempted, I'm coming
> > to the conclusion that I won't be able to do this through SQL.
> >
>
> I don't know whether you can or cannot do it in SQL. However, again I
> used PHP to solve my problem. I created PHP variables for each number I
> wanted to track. Then, when iterating through the query result set and
> writing the HTML rows of the table, I simply added the number I had for
> each row to the variable. For the final row of the table (the totals),
> all I had to do was print the contents of the variables.
>
> These are all simple, novice-type solutions but they did what I wanted
> using the tools I had at hand. You can see my first page done using
> these tactics at:
>
> http://www.simpaticus.com/flying/logbook.php
>
> And I'd be happy to email you the source code if you need it... it's not
> hard, though.
>
> Cheers,
>
> --
> Rodolfo J. Paiz <rpaiz(at)simpaticus(dot)com>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>