Re: Getting running totals

On Fri, Apr 08, 2005 at 01:33:53AM -0400, Tom Lane wrote:
> David <dbree(at)duo-county(dot)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.