On Tue, May 3, 2016 at 12:57 PM, Victor Yegorov <vyego...@gmail.com> wrote:
> 2016-05-03 22:48 GMT+03:00 Steve Clark <scl...@netwolves.com>: > >> select ip_dst as "Receiver" ,sum(bytes) as "RX Bytes" from acct_v9 where >> stamp_inserted >= '2016-04-26' and stamp_inserted <= '2016-04-30' and >> tag=246 group by ip_dst order by "RX Bytes" desc limit 10; > > > SELECT ip_dst AS "Receiver", > sum(bytes) AS "RX Bytes", > sum(sum(bytes)) OVER () AS "Grand Total" > FROM acct_v9 > WHERE stamp_inserted BETWEEN '2016-04-26' AND '2016-04-30' > AND tag=246 > GROUP BY ip_dst > ORDER BY "RX Bytes" DESC > LIMIT 10; > > I am not sure bout the LIMIT though, I hope window function will be > calculated after the LIMIT is applied. > > You will be disappointed, then. Limit will not impact the values within records, it only impacts which records are returned to the client. You have to move the limit into a subquery if you want it to apply before the window function computation. SELECT i, sum(sum(i)) OVER () FROM generate_series(1, 10) gs (i) GROUP BY i ORDER BY i LIMIT 5 ; P.S. 8.4 is long out of support - though fortunately you have access to window functions so the suggested approach can be made to work.
