CODEWITHSUNDEEP
sqlpostgresqldatetime
GIS Student
GIS Student

Reputation: 99

Sum Column of Integers Based on Timestamp in PostgreSQL

I am using PostgreSQL version 8.1. I have a table as follows:

          datetime     |    usage 
-----------------------+----------
2015-12-16 02:01:45+00 |    71.615
2015-12-16 03:14:42+00 |    43.000
2015-12-16 01:51:43+00 |    25.111
2015-12-17 02:05:26+00 |    94.087

I would like to add the integer values in the usage column based on the date in the datetime column.

Simply, I would like the output to look as below: 

          datetime     |    usage 
-----------------------+----------
2015-12-16             |    139.726
2015-12-17             |    94.087

I have tried SELECT dateTime::DATE, usage, SUM(usage) FROM tableName GROUP BY dateTime::DATE, lngusage; which does not perform as expected. Any assistance would be appreciated. Thanks in advance.

Upvotes: 2

Views: 1576

Answers (4)

viejoEngineer
viejoEngineer

Reputation: 404

In addition you could a window function.

SELECT DATETIME
,SUM(USAGE) OVER(PARTITION BY CAST(datetime AS DATE) ORDER BY datetime) AS Usage
FROM TableName

Upvotes: 0

Jay T.
Jay T.

Reputation: 307

SELECT to_char(datetime, 'format'), sum(usage)
FROM table
group by to_char(datetime, 'format')

Upvotes: 0

maraca
maraca

Reputation: 8743

This one is for postgreSQL, I see you added MySQL also.

SELECT
  dt
  SUM(usage),
FROM (
  SELECT
    DATE_TRUNC('day', datetime) dt,
    usage
  FROM
    tableName
) t
GROUP BY
  dt

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

Below query should give you the desired result:

select to_char(timestamp, 'YYYY-MM-DD') as time, sum(usage)
from table
group by time

Upvotes: 2

Related Questions