asked on Average each day over years
I don't even consider myself a novice especially considering how little I have to do this, but with google and searching EE I can usually figure this stuff out - so far, not this time.
I have this table![2017-11-27_9-59-19.jpg]()
and I need to pull out the average value for every day, of TagID 3006, over the course of several years.
It sounds so easy but I cant figure out how to "nest" a query like that.
I have this table
and I need to pull out the average value for every day, of TagID 3006, over the course of several years.
It sounds so easy but I cant figure out how to "nest" a query like that.
SQL
Last Comment
Mac
ASKER
Thanks for the reply but that's not quite what I'm looking for.
The values in the table are taken every 5 minutes throughout the day
I need the average of each day for a duration of several years. ie: 365 entries per year for only TagID 3006
1/1/2015 3.5
1/2/2015 1.2
1/3/2015 2.1
etc...
The values in the table are taken every 5 minutes throughout the day
I need the average of each day for a duration of several years. ie: 365 entries per year for only TagID 3006
1/1/2015 3.5
1/2/2015 1.2
1/3/2015 2.1
etc...
You keep saying "I need the average of each day" which you may mean, but your sample data implies "I need the average of daily values". If the latter, try this:
SELECT AVG(Value) FROM ThisTable WHERE TagID = '3006'
SELECT AVG(Value) FROM ThisTable WHERE TagID = '3006'
ASKER
The Table is much longer than shown, The sample data image is only there to show the column names however it also shows that Multiple TagID's are captured with each timestamp. this happens every day, 24 hours a day since 2012.
So I will have 288 entries every day for each TagID. I need the average of those 288 entries for each day, and I need to do that 365 times per year.
So I will have 288 entries every day for each TagID. I need the average of those 288 entries for each day, and I need to do that 365 times per year.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ryan - Bullseye! Minor adaptation (removed the @ from the table name) and I got the data I needed. Thank You
Geert - Very close but yours returned 2 entries for each day, one with a NULL tdYear and one with the year - certainly usable and better than where I was stuck though. I'll try to figure out why.
Thank you both very much, I am very grateful for your help Gentlemen.
Geert - Very close but yours returned 2 entries for each day, one with a NULL tdYear and one with the year - certainly usable and better than where I was stuck though. I'll try to figure out why.
Thank you both very much, I am very grateful for your help Gentlemen.
@Salad-Dodger
yea, I was trying to demonstrate the sample with temporary table, which need to be defined with @yourTableName. cheers glad that help.
yea, I was trying to demonstrate the sample with temporary table, which need to be defined with @yourTableName. cheers glad that help.
that was the intended return.
rollup can produce more than 1 group by
rollup can produce more than 1 group by
ASKER
... I've soooo much to learn.
...presuming the column named Value is the average for the day. Otherwise, there may not be enough information to answer the question.
Also, naming a column "Value" is a worst practice.