How to average/sum data in a day in SQL Server 2005

Question

I'm trying to average data in SQL Server 2005 in a day. Here is what my database look like this if I use simple query as

SELECT timestamp, FEED
FROM ROASTER_FEED
ORDER timestamp

Data:

timestamp               Feed    
02/07/2011 12:00:01     1246   
02/07/2011 12:00:01     1234    
02/07/2011 12:00:01     1387    
02/07/2011 12:00:02     1425   
02/07/2011 12:00:03     1263   
...    
02/07/2011 11:00:01     1153    
02/07/2011 11:00:01     1348    
02/07/2011 11:00:01     1387    
02/07/2011 11:00:02     1425    
02/07/2011 11:00:03     1223    
....   
03/07/2011 12:00:01     1226    
03/07/2011 12:00:01     1245    
03/07/2011 12:00:01     1384    
03/07/2011 12:00:02     1225    
03/07/2011 12:00:03     1363

I don't know how to average the feed when someone select a date in a month and it give display the average/sum of that day only.

For example, in the outcome, if I select day as 02/07/2011. It would give me something like this:

02/07/2011 1234 (average value/or sum)

is this data in a database, in a file, or what? What language are you using? — hvgotcodes, Sep 16 '11 at 21:17
What language? What platform? Is this a SQL question? If so, what server and version? — Oded, Sep 16 '11 at 21:17
We can't help you unless you tell us more information. Is this Excel? Access? MS SQL Server? — rlb.usa, Sep 16 '11 at 21:18
What is the Drye_data table structure? please give some example records from it. And put sql in the tags. — daniloquio, Sep 30 '11 at 20:04
that is not a database. Do you mean MS SQL 2005? it is possible to connect MS SQL Management Studio 2005 to a MS SQL 2008 database (among others) — Eonasdan, Sep 30 '11 at 20:08
Any particular reason to be grouping by timestamp? Why are you converting it to a char? Seems to me that the you shouldn't use group by. — daniloquio, Sep 30 '11 at 20:23
@daniloquio Not really. I just left it as how it showed up. The Group by doesn't affect the issue I'm having — Thao, Sep 30 '11 at 20:29

score 2 · Answer 1 · answered Sep 16 '11 at 21:51

One possibility, if you need to do this often enough: add three computed columns for day, month, year to your table. Those columns are computed automatically based on the timestamp column, and they're just integer values, so they're easy to use in a GROUP BY.

To do this, use these T-SQL statements:

ALTER TABLE dbo.ROASTER_FEED ADD TSDay AS DAY(timestamp) PERSISTED
ALTER TABLE dbo.ROASTER_FEED ADD TSMonth AS MONTH(timestamp) PERSISTED
ALTER TABLE dbo.ROASTER_FEED ADD TSYear AS YEAR(timestamp) PERSISTED

Now, you can easily select your data based on any day you wish:

SELECT TSDay, TSMonth, TSYear, SUM(FEED)   -- use AVG(FEED) for average values
FROM dbo.ROASTER_FEED
WHERE TSYear = 2011 AND TSMonth = 8   -- or whatever you want to grab from the table!
ORDER BY timestamp
GROUP BY TSDay, TSMonth, TSYear

score 1 · Answer 2 · answered Sep 29 '11 at 20:13

1

Well I'm not a SQL guy, but I'd expect something like:

SELECT SUM(ValueColumn) AS Total, AVG(ValueColumn) AS Average FROM YourTableName
WHERE RecordedTime >= @StartTime AND RecordedTime < @EndTime

(where @StartTime and @EndTime are parameters filled in by the calling code).

(It's not clear to me what will happen if no records are found... worth checking out.)

answered Sep 29 '11 at 20:13

Jon Skeet

1,421,763
867
9,128
9,194

I wish SQL is smarter in the time frame issue. :( I tried that. @ starttime and @ Endtime is only date selection, not time. I'm stucked – Thao Sep 29 '11 at 20:19
@Thao: That's not SQL's fault - you should make the calling code pass in the right DATETIME value. You probably *could* make it perform the addition in the SQL, but personally I'd do it in the calling code instead. It's not clear to me what your calling code *is* here - can you give more context? – Jon Skeet Sep 29 '11 at 20:24
Sorry, I'm a little slow. I'm a new learner. This is what I wrote. And it gives me from 12AM to 11:59:59PM SELECT CONVERT(char(10), timestamp, 105) AS 'day time', AVG(REPORTSROASTERFEED_RATE_VAL0) AS 'AVERAGE FEED DAY' FROM NIGHT_SHIFT GROUP BY CONVERT(char(10), timestamp, 105) HAVING (CONVERT(char(10), timestamp, 105) = @selectdate) – Thao Sep 29 '11 at 20:30
@Thao: No, ideally you shouldn't need to do any conversions - you should be able to pass in the right time *as a timestamp*. Avoid conversions wherever possible. – Jon Skeet Sep 29 '11 at 21:29
Thank you so much for quick help. So I tried again today, and here is what I came up with, of course, still problem. http://stackoverflow.com/questions/7615285/how-to-average-data-from-7am-to-7pm-of-one-day-in-sql-2005 – Thao Sep 30 '11 at 19:47

daniloquio · Accepted Answer · 2011-09-30T22:00:37.627

1

7:40am is 460 minutes after 00:00

7:40pm is 1180 minutes after 00:00

midnigth is 1440 minutes after 00:00

And DATEPART(hh, SomeDate)*60 + DATEPART(mi, SomeDate) gives you the amount of minutes after 00:00 for a given SomeDate

So, you could use:

SELECT
    AVG(Temperature) As Dayshift
FROM Drye_data
WHERE DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 460 AND 1180
      AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)

and for the Nightshift:

    SELECT
    AVG(Temperature) As Nigthshift
FROM Drye_data
WHERE (
        (DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 0 AND 460)
        AND @SelectedDate = DATEADD(dd, 1, CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME))
      )
      OR 
      (
         (DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 1180 AND 1440) AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)
      )

edited Sep 30 '11 at 22:00

answered Sep 30 '11 at 20:42

daniloquio

3,822
2
36
56

Thank you so much for this answer, I didn't think about break it down like that. It's so cool. However I forgot that the report has to be able to chose any day (another person can select a day, and the report is coming out on that day). Where would I put @selectdate? Thank you SO MUCH – Thao Sep 30 '11 at 20:48
You mean that the report is the average temperature for each shift in a specific day? – daniloquio Sep 30 '11 at 20:50
Yes, it is correct. Each shift in a specific day. And the day is chosen by viewrer. And the Night shift is tricky, because it's half of today, and half of the next day – Thao Sep 30 '11 at 20:54
@Thao - I edited the answer with what I think is the solution. I used this http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server to truncate the datetime to day level. – daniloquio Sep 30 '11 at 21:03
DAYSHIFT works like a charm. So amazing. However, Nightshift is a little off. Since it's from 7:40PM of today until 7:40AM next day. For example, Night shift of 30 Sept 2011 is from 7:40PM of 30 Sept 2011 until 7:40AM of 1 Oct 2011. Can I use (DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) Between 1180 to 1900 AND @selecteddate = .... – Thao Sep 30 '11 at 21:30
@Thao There were an error on the nigthshift part, I edited it and is corrected. Please test it :D – daniloquio Sep 30 '11 at 21:35
Something wrong with NightShift. I'm only able to get the average of 7PM to 11h59PM. It doesnt average the next morning :( – Thao Sep 30 '11 at 21:49
@Thao, this should work fine now; the selectedDate filter was not clear. You fit a lot in an expression we have here in Colombia: "juum pero usted pide más que deme!" Just joking, no ofense :D – daniloquio Sep 30 '11 at 22:03
AWESOME........... If you were here, I take you out for a meal :) (Sorry, I can't drink, get drunk too quick). But you can :) – Thao Sep 30 '11 at 22:09
Ok, no free meal for me, but at least fix the tags on your question jeje. By clicking edit you can do it and set the tags to sql/ sql-server / tsql / time . Good nigth. – daniloquio Sep 30 '11 at 22:13

score 0 · Answer 4 · answered Nov 07 '13 at 09:38

0

Check out some of the tutorials from here http://www.smallsql.de/doc/sql-functions/date-time/index.html ive been looking for something simialr and found that site useful and thought it may help

answered Nov 07 '13 at 09:38

Tariq Khalaf

89
1
1
10

Note that [link-only answers](http://meta.stackoverflow.com/tags/link-only-answers/info) are discouraged, SO answers should be the end-point of a search for a solution (vs. yet another stopover of references, which tend to get stale over time). Please consider adding a stand-alone synopsis here, keeping the link as a reference. – kleopatra Nov 07 '13 at 09:45

Shane Wealti · Answer 5 · 2011-09-16T23:31:32.543

0

SELECT COUNT(timestamp) as NumValues, 
       SUM(Feed) as ValuesSum, 
       AVG(Feed) as Average

  FROM ROASTER_FEED

 WHERE timestamp BETWEEN '1/1/2011'  AND '9/15/2011'

edited Sep 16 '11 at 23:31

answered Sep 16 '11 at 21:19

Shane Wealti

2,252
3
19
33

It doesn't work. It give me 614 same answer for every tick that timestamp recorded – Thao Sep 16 '11 at 21:25

Mikael Eriksson · Answer 6 · 2011-09-17T05:30:04.840

0

To get the average feed for one day you can use this.

declare @Date datetime

set @Date = '20110702'

select @Date as [timestamp], avg(FEED) as AvgFeed
from ROASTER_FEED
where [timestamp] >= @Date and 
      [timestamp] < dateadd(day, 1, @Date)

edited Sep 17 '11 at 05:30

answered Sep 16 '11 at 21:59

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 7 · answered Sep 16 '11 at 22:02

try this:

SELECT timestamp, sum(FEED)
FROM ROASTER_FEED
WHERE timestamp=....
GROUP BY timestamp

it could be slow if you are handling alot of data, in this case you should considering an additional table which holds the pre-calculated values for each day.

score 0 · Answer 8 · answered Sep 17 '11 at 04:49

@marc_s hit upon the preferred solution of using a computed columns to handle this but to do it on-the-fly, cast your timestamp data to shear off the time component. In my example, I cast it to a 10 character field that contains the 0 padded date.

;
WITH ROASTER_FEED ([timestamp],[Feed]) AS
(
SELECT CAST('02/07/2011 12:00:01' AS datetime),1246   
UNION ALL SELECT '02/07/2011 12:00:01',1234    
UNION ALL SELECT '02/07/2011 12:00:01',1387    
UNION ALL SELECT '02/07/2011 12:00:02',1425   
UNION ALL SELECT '02/07/2011 12:00:03',1263   

UNION ALL SELECT '02/07/2011 11:00:01',1153    
UNION ALL SELECT '02/07/2011 11:00:01',1348    
UNION ALL SELECT '02/07/2011 11:00:01',1387    
UNION ALL SELECT '02/07/2011 11:00:02',1425    
UNION ALL SELECT '02/07/2011 11:00:03',1223    

UNION ALL SELECT '03/07/2011 12:00:01',1226    
UNION ALL SELECT '03/07/2011 12:00:01',1245    
UNION ALL SELECT '03/07/2011 12:00:01',1384    
UNION ALL SELECT '03/07/2011 12:00:02',1225    
UNION ALL SELECT '03/07/2011 12:00:03',1363
)
SELECT
    -- Shear off the time value by forcing the date into date values
    -- If this was SQL Server 2008+, we'd just cast to date type and
    -- be done with it
    -- Chart for convert options
    -- http://msdn.microsoft.com/en-us/library/ms187928.aspx
    CONVERT(char(10), RF.[timestamp], 121) AS [timestamp]
,   COUNT(1) AS row_counts
,   SUM(RF.Feed) as ValuesSum 
,   AVG(RF.Feed) as Average
FROM 
    ROASTER_FEED RF
GROUP BY
    CONVERT(char(10), RF.[timestamp], 121)

Results in

timestamp   row_counts   ValuesSum  Average
2011-02-07  10           13091      1309
2011-03-07  5            6443       1288

Thank you. What I have been trying to search is this one CONVERT(char(10), RF.[timestamp], 121) AS [timestamp] — Thao, Sep 22 '11 at 19:32

How to average/sum data in a day in SQL Server 2005

8 Answers8

Linked

Related