Getting running totals
От | David |
---|---|
Тема | Getting running totals |
Дата | |
Msg-id | 20050408030028.GA3573@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: Getting running totals
Re: Getting running totals |
Список | pgsql-novice |
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?
В списке pgsql-novice по дате отправления: