Re: Getting running totals

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.