Re: Getting running totals - Mailing list pgsql-novice

On Fri, Apr 08, 2005 at 09:40:41AM -0600, Rodolfo J. Paiz 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. :-)

I liked Tom Lane's solution which he posted in this thread.  (there may
be others, too - I was still downloading emails when I began this reply.

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

I'd thought about something like this, too, but was thinking psql-ish
that it would be displayed, but, as you said, your client can show/hide
whatever you want.  I'm leaning toward PHP, also, and writing my own
client.  I've looked at the various (Linux) clients - well, pgaccess,
phppgadmin, if these are what you'd call clients, and they just seem to
generic to me.

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

Yeah.  After posting the original message, I sat down and wrote - well,
actually adapted another php script I had been playing with - and in
about 5 minutes, had it doing exactly what I wanted (in that respect).

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

That's what I did...

> 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

I'll give it a visit.

> And I'd be happy to email you the source code if you need it... it's not
> hard, though.

Thanks for the offer.  If I get stuck, I'll give you a shout.  I think
it's just a matter of getting headed in the right direction.  It seems
that there are so many alternatives for how to do things in PostgreSQL.

Basically, what I'm mostly concerned with is trying to use SQL
everywhere I can, and that's the primary reason for my questions.