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?