Thread: Get sum of sums

Get sum of sums

From

Steve Clark

Date:

03 May 2016, 22:48:19

Hi List, 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

Thanks,
Steve
--

Re: Get sum of sums

From

Victor Yegorov

Date:

03 May 2016, 22:58:07

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

Re: Get sum of sums

From

"David G. Johnston"

Date:

03 May 2016, 23:08:48

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.

Re: Get sum of sums

From

John R Pierce

Date:

03 May 2016, 23:12:45

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