how to sum up minutes to hours.

Question

how to sum up minutes to hours.

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 6, 2012 at 1:57 am

#96975

select Levels_ID,Reading_Date,sum(Fuel_Level)as fuel_level ,sum(Fuel_Temp)as fuel_temp,sum(Fuel_Volume) as fuel_volume from dbo.Levels as l
where l.Reading_Date >= dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0) AND l.Reading_Date < GETDATE() and
Simmons_PanelID=5479
group by Fuel_Level,Fuel_Temp,Fuel_Volume,Reading_Date,Levels_ID
the above query returns :
5281732012-08-01 00:00:00.0001559.1020.2411047.79
5281742012-08-01 00:00:00.000524.7020.554988.73
5281752012-08-01 00:00:00.000896.2017.6910632.20
5281762012-08-01 00:00:00.0001075.9020.186633.30
5281772012-08-01 00:00:00.000353.2018.104126.63
5282482012-08-01 00:15:00.0001559.1020.2411047.79
5282492012-08-01 00:15:00.000524.7020.544988.73
5282502012-08-01 00:15:00.000896.2017.6710632.20
5282512012-08-01 00:15:00.0001075.9020.186633.30
5282522012-08-01 00:15:00.000353.2018.054126.63
5283232012-08-01 00:30:00.0001559.1020.2311047.79
5283242012-08-01 00:30:00.000524.7020.534988.73
5283252012-08-01 00:30:00.000896.2017.6710632.20
5283262012-08-01 00:30:00.0001075.9020.176633.30
5283272012-08-01 00:30:00.000353.2018.014126.63
5283982012-08-01 00:45:00.0001559.1020.2311047.79
5283992012-08-01 00:45:00.000524.7020.524988.73
5284002012-08-01 00:45:00.000896.2017.6710632.20
5284012012-08-01 00:45:00.0001075.9020.176633.30
5284022012-08-01 00:45:00.000335.8017.953814.37
my question is I want to sum up the minutes into hrs , the data is upload for every 15minutes if I want to look data for every hour how to sum up the minutes to hour. plss.
cheers.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Depending on what you want to group by, you want something like this: -

SELECT Levels_ID,DATEADD(HOUR, DATEPART(HOUR,Reading_Date), DATEADD(DAY, 0, DATEDIFF(DAY, 0, Reading_Date))) AS Reading_Date,

SUM(fuel_level),SUM(fuel_temp),SUM(fuel_volume)

FROM (

SELECT Levels_ID,Reading_Date,fuel_level,fuel_temp,fuel_volume

FROM (

select Levels_ID,Reading_Date,sum(Fuel_Level)as fuel_level ,sum(Fuel_Temp)as fuel_temp,sum(Fuel_Volume) as fuel_volume from dbo.Levels as l

where l.Reading_Date >= dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0) AND l.Reading_Date < GETDATE() and

Simmons_PanelID=5479

group by Fuel_Level,Fuel_Temp,Fuel_Volume,Reading_Date,Levels_ID

)a(Levels_ID,Reading_Date,fuel_level,fuel_temp,fuel_volume)

) a

GROUP BY Levels_ID, DATEADD(HOUR, DATEPART(HOUR,Reading_Date), DATEADD(DAY, 0, DATEDIFF(DAY, 0, Reading_Date)));

Or this: -

SELECT DATEADD(HOUR, DATEPART(HOUR,Reading_Date), DATEADD(DAY, 0, DATEDIFF(DAY, 0, Reading_Date))) AS Reading_Date,

SUM(fuel_level),SUM(fuel_temp),SUM(fuel_volume)

FROM (SELECT Levels_ID,Reading_Date,fuel_level,fuel_temp,fuel_volume

FROM (

select Levels_ID,Reading_Date,sum(Fuel_Level)as fuel_level ,sum(Fuel_Temp)as fuel_temp,sum(Fuel_Volume) as fuel_volume from dbo.Levels as l

where l.Reading_Date >= dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0) AND l.Reading_Date < GETDATE() and

Simmons_PanelID=5479

group by Fuel_Level,Fuel_Temp,Fuel_Volume,Reading_Date,Levels_ID

)a(Levels_ID,Reading_Date,fuel_level,fuel_temp,fuel_volume)

) a

GROUP BY DATEADD(HOUR, DATEPART(HOUR,Reading_Date), DATEADD(DAY, 0, DATEDIFF(DAY, 0, Reading_Date)));

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

vinay.varaala (9/6/2012)
select Levels_ID,Reading_Date,sum(Fuel_Level)as fuel_level ,sum(Fuel_Temp)as fuel_temp,sum(Fuel_Volume) as fuel_volume from dbo.Levels as l
where l.Reading_Date >= dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0) AND l.Reading_Date < GETDATE() and
Simmons_PanelID=5479
group by Fuel_Level,Fuel_Temp,Fuel_Volume,Reading_Date,Levels_ID
the above query returns :
5281732012-08-01 00:00:00.0001559.1020.2411047.79
5281742012-08-01 00:00:00.000524.7020.554988.73
5281752012-08-01 00:00:00.000896.2017.6910632.20
5281762012-08-01 00:00:00.0001075.9020.186633.30
5281772012-08-01 00:00:00.000353.2018.104126.63
5282482012-08-01 00:15:00.0001559.1020.2411047.79
5282492012-08-01 00:15:00.000524.7020.544988.73
5282502012-08-01 00:15:00.000896.2017.6710632.20
5282512012-08-01 00:15:00.0001075.9020.186633.30
5282522012-08-01 00:15:00.000353.2018.054126.63
5283232012-08-01 00:30:00.0001559.1020.2311047.79
5283242012-08-01 00:30:00.000524.7020.534988.73
5283252012-08-01 00:30:00.000896.2017.6710632.20
5283262012-08-01 00:30:00.0001075.9020.176633.30
5283272012-08-01 00:30:00.000353.2018.014126.63
5283982012-08-01 00:45:00.0001559.1020.2311047.79
5283992012-08-01 00:45:00.000524.7020.524988.73
5284002012-08-01 00:45:00.000896.2017.6710632.20
5284012012-08-01 00:45:00.0001075.9020.176633.30
5284022012-08-01 00:45:00.000335.8017.953814.37
my question is I want to sum up the minutes into hrs , the data is upload for every 15minutes if I want to look data for every hour how to sum up the minutes to hour. plss.
cheers.

Since you're new here, have a look at the first link in my signature line below for how to post data to get tested answers more quickly.

The other thing that you have to recognize is that you must lose the unique data in the output if you actually want correct output. For example, does it really make sense to have the Levels_ID in the output with a sum for many rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)