Get sum of sums

2016-05-03 22:48 GMT+03:00 Steve Clark <sclark@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.

--
Victor Y. Yegorov

On Tue, May 3, 2016 at 12:57 PM, Victor Yegorov <vyegorov@gmail.com> wrote:

2016-05-03 22:48 GMT+03:00 Steve Clark <sclark@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.

On 5/3/2016 12:48 PM, Steve Clark wrote:

I have the following table that has netflow data. I can get the top
ten receivers by the query below - but I would also like to get
at the same time a grand total of the RX Bytes. I am not exactly sure
how to do it. This is with version 8.4.

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;
Receiver | RX Bytes
----------------+-------------
172.24.110.93 | 40363536915
172.24.110.81 | 6496041533
172.24.110.123 | 4891514009
172.24.16.10 | 4540333516
172.24.110.151 | 4101253631
192.168.198.71 | 3303066724
172.24.110.121 | 2529532947
172.24.110.101 | 2506527294
172.21.64.99 | 2472842640
172.24.110.83 | 2232550271

a single query can only return a set of rows with the same fields.
your grand total of RX Bytes is a scalar value. I mean, I *suppose*
you could generate it as an additional query with a union, something
like this...

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

union

select 'Total' as "Reciever", sum(bytes) as "RX Bytes" from acct_v9
where stamp_inserted >= '2016-04-26' and stamp_inserted <=
'2016-04-30' and tag=246;

--
john r pierce, recycling bits in santa cruz