Re: Getting running totals

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.

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