Re: Get sum of sums

On Tue, May 3, 2016 at 12:57 PM, Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:

> 2016-05-03 22:48 GMT+03:00 Steve Clark <sclark(at)netwolves(dot)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.