Re: Getting running totals - Mailing list pgsql-novice

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@simpaticus.com>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)