Re: Getting running totals

David <dbree(at)duo-county(dot)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