CODEWITHSUNDEEP
sqlpostgresql
user1564173
user1564173

Reputation: 31

Postgres group by date(timestamp) and sum value.

I have below table entries. 

mydb=> SELECT username, input_bytes, output_bytes, updated_at from radius_acct;

 username | input_bytes | output_bytes |       updated_at
----------+-------------+--------------+------------------------
 sandeep  |      255081 |        42154 | 2014-05-08 09:34:13+00
 sandeep  |      250281 |        42254 | 2014-05-09 09:34:14+00
 prashant |      258081 |        42354 | 2014-05-10 09:34:15+00
 arun     |         493 |         1215 | 2014-05-17 11:16:53+00
 arun     |        9421 |         3657 | 2014-05-17 11:21:38+00
 arun     |       18325 |         4504 | 2014-05-17 17:12:53+00
 prashant |      254081 |        42454 | 2014-05-11 09:34:16+00
 raj      |      255081 |        42554 | 2014-05-12 09:34:17+00
 raj      |      250681 |        42554 | 2014-05-13 09:34:18+00
 raj      |      253081 |        42654 | 2014-05-14 09:34:19+00
 kumar    |      295081 |        47254 | 2014-05-15 09:34:16+00
(11 rows)

I want to sum the input and output bytes and display it in MB and group the username by date.

My query do not group the date and sum it up and displays a similar output as shown below: 

mydb=> SELECT username, input_bytes, output_bytes, ROUND((SUM(input_bytes + output_bytes)/1048576)::decimal, 2) AS total, date(updated_at)  from radius_acct group by username, input_bytes, output_bytes, updated_at ORDER BY username ASC ;
 username | input_bytes | output_bytes | total |    date
----------+-------------+--------------+-------+------------
 arun     |         493 |         1215 |  0.00 | 2014-05-17
 arun     |        9421 |         3657 |  0.01 | 2014-05-17
 arun     |       18325 |         4504 |  0.02 | 2014-05-17
 kumar    |      295081 |        47254 |  0.33 | 2014-05-15
 prashant |      254081 |        42454 |  0.28 | 2014-05-11
 prashant |      258081 |        42354 |  0.29 | 2014-05-10
 raj      |      250681 |        42554 |  0.28 | 2014-05-13
 raj      |      253081 |        42654 |  0.28 | 2014-05-14
 raj      |      255081 |        42554 |  0.28 | 2014-05-12
 sandeep  |      250281 |        42254 |  0.28 | 2014-05-09
 sandeep  |      255081 |        42154 |  0.28 | 2014-05-08

Expected output:

username | input_bytes | output_bytes | total |    date
----------+-------------+--------------+-------+------------
 **arun     |       28239 |         9376 |  0.03 | 2014-05-17**
 kumar    |      295081 |        47254 |  0.33 | 2014-05-15
 prashant |      254081 |        42454 |  0.28 | 2014-05-11
 prashant |      258081 |        42354 |  0.29 | 2014-05-10
 raj      |      250681 |        42554 |  0.28 | 2014-05-13
 raj      |      253081 |        42654 |  0.28 | 2014-05-14
 raj      |      255081 |        42554 |  0.28 | 2014-05-12
 sandeep  |      250281 |        42254 |  0.28 | 2014-05-09
 sandeep  |      255081 |        42154 |  0.28 | 2014-05-08

Row one arun is total of three rows.

Upvotes: 1

Views: 3316

Answers (1)

Rahul
Rahul

Reputation: 77876

Try this once

SELECT username, 
sum(input_bytes) as totalinputbytes, 
sum(output_bytes) as totaloutputbytes, 
ROUND((SUM(input_bytes + output_bytes)/1048576)::decimal, 2) AS total, 
date(updated_at) as update_date
from radius_acct 
group by username, date(updated_at)
ORDER BY username;

Upvotes: 5

Related Questions