Thread: Getting running totals

This may be another elementary question - I found some hints in the
archives that may have answered my question, but not sure..

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)

I got the first two columns with this view (mosums)

 SELECT to_char(transact.t_date::timestamp with time zone,
         'Mon'::text) AS "Month", sum(transact.t_cost) AS "Month Ttl"
   FROM transact
   GROUP BY to_char(transact.t_date::timestamp with time zone,'Mon'::text);

I found that this didn't always get the months rows ordered correctly,
and it erred if I added ORDER BY (the below) at the end of the query - saying
it couldn't find column "Month", so I added another view (xx) - I'll rename
 them when I get them working..

  SELECT * from mosums ORDER BY date_part('mon'::text,
                         to_date(mosums."Month", 'Mon'::text));

Am I correct that I had to do this?

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.  The closest
I could come for an answer was a thread on pgsql-php with subject
"grouping query results".  This person seemed to be interested in
something somewhat similar to this, and it appeared that the general
consensus was that he'd be best off doing it through something like PHP.
Would this be correct for my case?  Or is there some way in SQL to get a
running total?

David <dbree@duo-county.com> writes:
>  SELECT to_char(transact.t_date::timestamp with time zone,
>          'Mon'::text) AS "Month", sum(transact.t_cost) AS "Month Ttl"
>    FROM transact
>    GROUP BY to_char(transact.t_date::timestamp with time zone,'Mon'::text);

> I found that this didn't always get the months rows ordered correctly,

Right --- the above says nothing about what order you want the results
in.

> and it erred if I added ORDER BY (the below) at the end of the query

Yeah, because you can't ORDER BY anything except a GROUP BY item or a
function of a GROUP BY item.  You know and I know that both of the
expressions you were using depend only on the month part of the date,
but the SQL parser doesn't know that (and shouldn't be expected to,
IMHO).  So it thinks the ORDER BY expression isn't certain to yield a
unique result for each group, which makes the query ambiguous.

You could ORDER BY the same thing you grouped by, viz
 ORDER BY to_char(transact.t_date::timestamp with time zone,'Mon'::text)
but of course that produces a textual ordering (Apr, Aug, etc) because
to_char has a text result.  Not what you want.

What you have to do is think of a GROUP BY expression that can be a
foundation for both the numeric month ordering and the textual month
name output that you want.  There are any number of ways to do this,
but the first one that came to mind for me is to group by
date_trunc('month'), which reduces a date to the first of its month:

SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
       sum(transact.t_cost) AS "Month Ttl"
  FROM transact
  GROUP BY date_trunc('month', t_date)
  ORDER BY date_trunc('month', t_date);

            regards, tom lane

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>

On Fri, Apr 08, 2005 at 01:33:53AM -0400, Tom Lane wrote:
> David <dbree@duo-county.com> writes:
> >  SELECT to_char(transact.t_date::timestamp with time zone,
> >          'Mon'::text) AS "Month", sum(transact.t_cost) AS "Month Ttl"
> >    FROM transact
> >    GROUP BY to_char(transact.t_date::timestamp with time zone,'Mon'::text);
>
> > I found that this didn't always get the months rows ordered correctly,
>
> Right --- the above says nothing about what order you want the results
> in.
>
> > and it erred if I added ORDER BY (the below) at the end of the query
>
> Yeah, because you can't ORDER BY anything except a GROUP BY item or a
> function of a GROUP BY item.  You know and I know that both of the
> expressions you were using depend only on the month part of the date,
> but the SQL parser doesn't know that (and shouldn't be expected to,
> IMHO).  So it thinks the ORDER BY expression isn't certain to yield a
> unique result for each group, which makes the query ambiguous.

It does make sense that you need to ORDER BY something that is defined.

> You could ORDER BY the same thing you grouped by, viz
>  ORDER BY to_char(transact.t_date::timestamp with time zone,'Mon'::text)
> but of course that produces a textual ordering (Apr, Aug, etc) because
> to_char has a text result.  Not what you want.

Exactly.  Of course what I want is to ORDER BY the numeric order of the
months.

> What you have to do is think of a GROUP BY expression that can be a
> foundation for both the numeric month ordering and the textual month
> name output that you want.  There are any number of ways to do this,
> but the first one that came to mind for me is to group by
> date_trunc('month'), which reduces a date to the first of its month:

I hadn't caught that function.  There are so many ways to do things (as
you said above).  I guess it's obvious that I'm still learning.

> SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
>        sum(transact.t_cost) AS "Month Ttl"
>   FROM transact
>   GROUP BY date_trunc('month', t_date)
>   ORDER BY date_trunc('month', t_date);

That does it in a single command.  I'm not sure I understand the full
implications of what's occurring.  It seems to me that ORDER BY can be
sort of picky about what it will accept.

David <dbree@duo-county.com> writes:
> On Fri, Apr 08, 2005 at 01:33:53AM -0400, Tom Lane wrote:
>> SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
>> sum(transact.t_cost) AS "Month Ttl"
>> FROM transact
>> GROUP BY date_trunc('month', t_date)
>> ORDER BY date_trunc('month', t_date);

> That does it in a single command.  I'm not sure I understand the full
> implications of what's occurring.  It seems to me that ORDER BY can be
> sort of picky about what it will accept.

No pickier than the SELECT output list --- the rules are the same, in
fact.  The reason that this is OK

    SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^

is that the parser sees that the part I underlined matches the GROUP BY
expression, and so it knows that the entire expression is well defined:
it will only have one value for each grouping value.  The results of a
grouped query have to either have that property, or be aggregate
functions (which arrive at a single value per group too, of course).

            regards, tom lane

I haven't been following this discussion as closely as I could have, but is
it also possible in Postgres to ORDER BY the ordinal position of the item in
the SELECT list, such as ORDER BY 1  or ORDER BY 3 , etc? That isn't as
descriptive as I like, but it worked in SQL Server, and I think it also
worked with calculated fields, GROUP BY, etc...

Thanks, David


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, April 08, 2005 3:00 PM
To: David
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Getting running totals


David <dbree@duo-county.com> writes:
> On Fri, Apr 08, 2005 at 01:33:53AM -0400, Tom Lane wrote:
>> SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
>> sum(transact.t_cost) AS "Month Ttl"
>> FROM transact
>> GROUP BY date_trunc('month', t_date)
>> ORDER BY date_trunc('month', t_date);

> That does it in a single command.  I'm not sure I understand the full
> implications of what's occurring.  It seems to me that ORDER BY can be
> sort of picky about what it will accept.

No pickier than the SELECT output list --- the rules are the same, in
fact.  The reason that this is OK

    SELECT to_char(date_trunc('month', t_date), 'Mon') AS "Month",
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^

is that the parser sees that the part I underlined matches the GROUP BY
expression, and so it knows that the entire expression is well defined:
it will only have one value for each grouping value.  The results of a
grouped query have to either have that property, or be aggregate
functions (which arrive at a single value per group too, of course).

            regards, tom lane

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

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.

On Fri, Apr 08, 2005 at 12:11:25PM -0500, Bob Henkel wrote:
> 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.

I'd thought about something like this, too, but my goal is to eliminate
as much programming as possible and let SQL do the work.  I got it to
work by creating a view to do the grouping, and then having another view
call up that view and then sort on the column.  However, so far, Tom
Lane showed me how to do it in a single command.  Also, Rodolfo has
verified some of my assumtions.  Thanks to all for the replies.

>  On Apr 8, 2005 10:40 AM, Rodolfo J. Paiz <rpaiz@simpaticus.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)

[question was about sorting et.al.]

On Fri, 2005-04-08 at 16:09 -0500, David wrote:
> 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.

I know exactly what you mean. I just started recently on learning HTML,
PHP, and PEAR (for database access and other modules, so I don't have to
reinvent the wheel). I'm constantly finding new things to learn, and of
course I'm only 1% of where I want to go. :-)

I've got the "news" items in Simpaticus.com to be database-driven, and
the feedback/rating forms for each important document, and I've got the
data for my flight log into PostgreSQL using PhpPgAdmin and am now
working to write the reports I want.

I really am just getting started, but feel free to shout at any time.
Maybe you can help me, or vice versa. If you want to IM, email me and
I'll give you the account. Happy to give it to anyone else, too... just
not posting it here since it goes in the archives and then becomes spam.

Cheers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>