Monday, April 28. 2008How to calculate Running Totals and Sums in SQLPrinter FriendlyComments
Display comments as
(Linear | Threaded)
There is also a way to do it without subqueries, using pl/pgsql.
I described it in a blogpost here: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
The sub-select solution doesn't look very good for few reasons:
1) it's not readable 2) subqueries will use lots of temporary space when materialize during execution and it's generally not efficient for big datasets. What postgres desperately needs to compete with commercial database in Datawarehousing is a oracle-like window analytical functions. I hope 8.4 will bring us closer to this!
#2
on
2008-04-30 19:31
Question 1 and Solution to it just solved my problem. Thanks very much.
Vish
#3
on
2009-12-15 10:52
This is my sql query but not calculate sum if third value is get zero value please help me
SELECT SUM(strForAsia)+ SUM(strFor20Europ) + SUM(strFor20USA ) as str20Total FROM vw20ForAsia full join vw20ForEurop on vw20ForEurop.ClientID= vw20ForAsia.ClientID
#4
on
2010-03-03 08:30
Try using coalesce around all your sums so something like :
COALESCE(SUM(strForAsia),0) + ... COALESCE(SUM(strFor20USA),0) If you have all nulls in a column it will return null. Null + 0 -> null coalesce should fix that so if your sum is null, it will return 0.
#4.1
on
2010-03-05 02:48
|
Entry's LinksQuicksearchCalendar
Categories
Blog Administration |
One thing that is pretty neat about windowing functions in PostgreSQL 8.4 aside from built-in windowing functions (row_number(), rank(), lead(), lag(), dense_rank(), percent_rank(), cume_dist(), first_value, last_value, nth_value) as documented in the man
Tracked: May 27, 17:01
Tracked: Jul 14, 15:00