CODEWITHSUNDEEP
mysqlsql
renevdkooi
renevdkooi

Reputation: 1643

query to group by datetime and total values of another

I have a table like this

"id, capture_date, data1, data2"

The capture_date field is a DATETIME field in mysql. A script is run to capture some data (data1 INT and data2 INT)

Now I would like to total the data1 and data2 per hour. So I need to group the capture_date field for the hour but total the data1 and data2 fields.

Any one knows how? Ive hardly worked with DATETIME field. ps: The capture hasn't started yet, and I am still able to change the fieldtype if needed.

Upvotes: 0

Views: 66

Answers (1)

rabudde
rabudde

Reputation: 7722

Transform DATETIME field with DATE_FORMAT so you get the hours per day and group over this expression

SELECT DATE_FORMAT(capture_date,'%Y-%m-%d %H:00') capture_date,SUM(data1) data1,SUM(data2) data FROM <table> GROUP BY DATE_FORMAT(capture_date,'%Y-%m-%d %H:00')

Upvotes: 1

Related Questions