adding up a total per day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding up a total per day

Author

Topic

mpooley
Starting Member

13 Posts

Posted - 2011-01-06 : 07:27:13

Hi
I have a table called BoilerState which has a 5 columns
ID,temperature,sensorID,readingdate and Boilerstate
a new row is created every minute.
I want to find the Total number of minutes that the boiler was on for each day. where the sensorID =8
the boilerstate is 0 for off 1 for on.

I have tried a few things but am stuck
This is the best i have done so far
it returns columns but it is not what i wanted

SELECT datepart(day,ReadingDate), SUM(BoilerState) AS TotalMins
FROM BoilerState
where RoomSensorID=8

group BY ReadingDate

Michael D Pooley

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 07:30:04

What are you getting vs what do you want?

Are the values wrong? too few records? too many records?

How about some sample data

Read these to see what we need

Please read this information about what YOU need to understand for us to help you
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

and then read this information on how to post the relevant information
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Poor planning on your part does not constitute an emergency on my part.

mpooley
Starting Member

13 Posts

Posted - 2011-01-06 : 07:40:55

Sorry
I would expect a daily total of anywhere between 100 to 800 mins for the 3 months data that there is so far.
I am getting two columns returned
one with no column name and the other as TotalMins
the first column is random (day no's) between 1 and 31 in no order that i can see and 82792 results which is far more than the approx 90 days of data
the second column contains numbers between 0 and 31 seemingly at random also
the only difference is the second column contains zeros (quite a lot)
so in truth i dont understand the results i am getting

Mike

Michael D Pooley

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 07:50:50

Not sure if you want the random day #'s or Day Of Week, but you should be filtering ReadingDate for the range expected

SELECT DATENAME(WEEKDAY,ReadingDate) as DayofWeek, SUM(BoilerState) AS TotalMins
FROM BoilerState
WHERE ReadingDate >= '20100101' and ReadingDate <= '20101231' and RoomSensorID=8
GROUP BY DATENAME(WEEKDAY,ReadingDate)

Poor planning on your part does not constitute an emergency on my part.

mpooley
Starting Member

13 Posts

Posted - 2011-01-06 : 08:01:18

Thankyou! that is great.
It returns 7 rows weds to teusday! with total minutes
so I am nearly there
Sorry if i didn't make it clear though.
I wanted all approx 90 days totals
would that be possible?

thanks for your help it is much appreciated
Mike

Michael D Pooley

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 15:39:49

Can you clarify what you want as a result please?

Poor planning on your part does not constitute an emergency on my part.

mpooley
Starting Member

13 Posts

Posted - 2011-01-06 : 19:39:30

I want a table with 2 columns
1st col = date
2nd col = total minutes
(eg number of records where the boilerstate=1 for that date)

thanks for you patience

mike

Michael D Pooley

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-06 : 20:07:38

SELECT D ReadingDate , SUM(BoilerState) AS TotalMins
FROM BoilerState
WHERE ReadingDate >= '20100101' and ReadingDate <= '20101231' and RoomSensorID=8
GROUP BY ReadingDate
ORDER BY ReadingDate

Just select the date column instead of the weekday like my other one.

Poor planning on your part does not constitute an emergency on my part.

mpooley
Starting Member

13 Posts

Posted - 2011-01-07 : 08:01:11

Sorry no that isn't working either - I had to delete the D before reading date as is gave me an error 'invalid column name D'

when i removed the D and ran it
i got two columns like i wanted but the dates weren't just one per day IYSWIM they were every minute of the day, so instead of collating all these readings into 1 per day they were still in the original format of 1 per minute.
if i could post a screen shot that might help but i dont know how i'll try this and see if it works:

i do appreciate your time and don't expect you to waste too much time on my problem, i honestly thought this one would be easy ! that just shows my ignorance lol

Michael D Pooley

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-07 : 08:03:48

Ahh.. I see.
Here you go, (Sorry about the rogue letter up there)

Strip the time off the date, then you will get the daily aggregates.

SELECT DATEADD(D,0,datediff(d,0,ReadingDate)) as DateOnly , SUM(BoilerState) AS TotalMins
FROM BoilerState
WHERE ReadingDate >= '20100101' and ReadingDate <= '20101231' and RoomSensorID=8
GROUP BY DATEADD(D,0,datediff(d,0,ReadingDate))
ORDER BY DATEADD(D,0,datediff(d,0,ReadingDate))

Alternately you could use convert

SELECT CONVERT(varchar(10),ReadingDate,101) as DateOnly , SUM(BoilerState) AS TotalMins
FROM BoilerState
WHERE ReadingDate >= '20100101' and ReadingDate <= '20101231' and RoomSensorID=8
GROUP BY CONVERT(varchar(10),ReadingDate,101)
ORDER BY CONVERT(varchar(10),ReadingDate,101)

But you may see odd results sorting by the result varchar in that one.

Poor planning on your part does not constitute an emergency on my part.

mpooley
Starting Member

13 Posts

Posted - 2011-01-07 : 08:17:15

Thats great!

Much appreciated :)

Michael D Pooley

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources