CODEWITHSUNDEEP
sqlsql-server
Brad
Brad

Reputation: 635

SUM columns by hour (using DATETIME column)

I have a database that I need to sum 2 values using the datetime column. Example:

Date                Offered
4/16/2012 08:00:00        2
4/16/2012 08:30:00       18
4/16/2012 09:00:00       14
4/16/2012 09:30:30       42

I need to sum the values of 08:00:00 with 08:30:00 (total: 20) and 09:00:00 with 09:30:00 (total: 56) and so on.

Upvotes: 2

Views: 11605

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280413

SELECT [Hour] = DATEPART(HOUR, [Date]), Offered = SUM(Offered)
  FROM dbo.table_name
  WHERE [Date] >= '20120416'
  AND [Date] < '20120417'
  GROUP BY DATEPART(HOUR, [Date])
  ORDER BY [Hour];

Upvotes: 0

Nathan Koop
Nathan Koop

Reputation: 25197

This should work for you

select datepart(hour,myDate), SUM(Offered)
from myTable
group by 
    datepart(hour,myDate), 
    dateadd(d, 0, datediff(d, 0, myDate))

You need to group by both the hour and the date if you want it summed by individual day, otherwise you'll include other days (IE April 15 etc...)

Upvotes: 6

Brian
Brian

Reputation: 1894

Use a DatePart function. Syntax depends on your database

Select DatePart("year", Date), Sum(Offered)
From table1
Group By DatePart("year", Date)

Upvotes: -1

xQbert
xQbert

Reputation: 35333

Your pseudo code

Select HOUR(date), sum(offered) as sumO
FROM YourTable
Group By Hour(date)

Hour(date) would need to be altered to the correct syntax for the database you're working with.

Upvotes: 2

Related Questions