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 table2017-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.
LVL 1
Salad-DodgerInstrumentationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
SELECT Value FROM ThisTable WHERE TagID = '3006'

...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.
0
Salad-DodgerInstrumentationAuthor Commented:
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...
0
Paul MacDonaldDirector, Information SystemsCommented:
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'
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Salad-DodgerInstrumentationAuthor Commented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in general, AVG function should worked for you. what you need is to group the data and specify whether you want to filter it by Year or other criterion.

see if example below give you some ideas how to do that?

declare @yourTable table
(
	id int,
	TagID int,
	VALUE money,
	TIMEandDATE datetime,
	QUANTITY int,
	_COMMENT varchar(100)
)

insert into @yourTable
values
(70137, 3006, 0.6, '2012-08-01 00:00:00.673', 192, null),
(70138, 3023, 261, '2012-08-01 00:00:00.657', 192, null),
(70139, 3024, 34, '2012-08-01 00:00:00.657', 192, null),
(70140, 3026, 0, '2012-08-01 00:00:00.673', 192, null),
(70141, 3025, 40, '2012-08-01 00:00:00.657', 192, null),

(70137, 3006, 2.6, '2012-08-01 00:10:00.673', 192, null),
(70138, 3023, 41, '2012-08-01 00:10:00.657', 192, null),
(70139, 3024, 10.4, '2012-08-01 00:10:00.657', 192, null),
(70140, 3026, 0.21, '2012-08-01 00:10:00.673', 192, null),
(70141, 3025, 60, '2012-08-01 00:10:00.657', 192, null),

(70137, 3006, 30.2, '2012-08-01 00:20:00.673', 192, null),
(70138, 3023, 2.1, '2012-08-01 00:20:00.657', 192, null),
(70139, 3024, 20.34, '2012-08-01 00:20:00.657', 192, null),
(70140, 3026, 3.22, '2012-08-01 00:20:00.673', 192, null),
(70141, 3025, 20, '2012-08-01 00:20:00.657', 192, null),

(70137, 3006, 74.6, '2012-08-01 00:30:00.673', 192, null),
(70138, 3023, 31, '2012-08-01 00:30:00.657', 192, null),
(70139, 3024, 25.32, '2012-08-01 00:30:00.657', 192, null),
(70140, 3026, 10, '2012-08-01 00:30:00.673', 192, null),
(70141, 3025, 70, '2012-08-01 00:30:00.657', 192, null),

(70137, 3006, 84.4, '2012-08-01 00:40:00.673', 192, null),
(70138, 3023, 21, '2012-08-01 00:40:00.657', 192, null),
(70139, 3024, 32, '2012-08-01 00:40:00.657', 192, null),
(70140, 3026, 200, '2012-08-01 00:40:00.673', 192, null),
(70141, 3025, 90, '2012-08-01 00:40:00.657', 192, null);

insert into @yourTable
values
(70137, 3006, 10.2, '2012-08-02 00:00:00.673', 192, null),
(70138, 3023, 4.5, '2012-08-02 00:00:00.657', 192, null),
(70139, 3024, 42.3, '2012-08-02 00:00:00.657', 192, null),
(70140, 3026, 2.401, '2012-08-02 00:00:00.673', 192, null),
(70141, 3025, 140.32, '2012-08-02 00:00:00.657', 192, null),

(70137, 3006, 11.6, '2012-08-02 00:10:00.673', 192, null),
(70138, 3023, 321, '2012-08-02 00:10:00.657', 192, null),
(70139, 3024, 320.4, '2012-08-02 00:10:00.657', 192, null),
(70140, 3026, 90.21, '2012-08-02 00:10:00.673', 192, null),
(70141, 3025, 21.43, '2012-08-02 00:10:00.657', 192, null),

(70137, 3006, 130.2, '2012-08-02 00:20:00.673', 192, null),
(70138, 3023, 23.1, '2012-08-02 00:20:00.657', 192, null),
(70139, 3024, 7.34, '2012-08-02 00:20:00.657', 192, null),
(70140, 3026, 322, '2012-08-02 00:20:00.673', 192, null),
(70141, 3025, 220, '2012-08-02 00:20:00.657', 192, null),

(70137, 3006, 50.6, '2012-08-02 00:30:00.673', 192, null),
(70138, 3023, 3.31, '2012-08-02 00:30:00.657', 192, null),
(70139, 3024, 25.32, '2012-08-02 00:30:00.657', 192, null),
(70140, 3026, 30, '2012-08-02 00:30:00.673', 192, null),
(70141, 3025, 7.3, '2012-08-02 00:30:00.657', 192, null),

(70137, 3006, 5.4, '2012-08-02 00:40:00.673', 192, null),
(70138, 3023, 31, '2012-08-02 00:40:00.657', 192, null),
(70139, 3024, 93, '2012-08-02 00:40:00.657', 192, null),
(70140, 3026, 24.04, '2012-08-02 00:40:00.673', 192, null),
(70141, 3025, 90.94, '2012-08-02 00:40:00.657', 192, null);

insert into @yourTable
values
(70137, 3006, 30.6, '2012-08-03 00:00:00.673', 192, null),
(70138, 3023, 2.61, '2012-08-03 00:00:00.657', 192, null),
(70139, 3024, 3.24, '2012-08-03 00:00:00.657', 192, null),
(70140, 3026, 80, '2012-08-03 00:00:00.673', 192, null),
(70141, 3025, 2.20, '2012-08-03 00:00:00.657', 192, null),

(70137, 3006, 23.6, '2012-08-03 00:10:00.673', 192, null),
(70138, 3023, 451, '2012-08-03 00:10:00.657', 192, null),
(70139, 3024, 103.4, '2012-08-03 00:10:00.657', 192, null),
(70140, 3026, 0.521, '2012-08-03 00:10:00.673', 192, null),
(70141, 3025, 60.2, '2012-08-03 00:10:00.657', 192, null),

(70137, 3006, 303.2, '2012-08-03 00:20:00.673', 192, null),
(70138, 3023, 22.1, '2012-08-03 00:20:00.657', 192, null),
(70139, 3024, 250.34, '2012-08-03 00:20:00.657', 192, null),
(70140, 3026, 53.22, '2012-08-03 00:20:00.673', 192, null),
(70141, 3025, 230, '2012-08-03 00:20:00.657', 192, null),

(70137, 3006, 274.6, '2012-08-03 00:30:00.673', 192, null),
(70138, 3023, 341, '2012-08-03 00:30:00.657', 192, null),
(70139, 3024, 265.32, '2012-08-03 00:30:00.657', 192, null),
(70140, 3026, 10, '2012-08-03 00:30:00.673', 192, null),
(70141, 3025, 780, '2012-08-03 00:30:00.657', 192, null),

(70137, 3006, 8.24, '2012-08-03 00:40:00.673', 192, null),
(70138, 3023, 2.1, '2012-08-03 00:40:00.657', 192, null),
(70139, 3024, 32.2, '2012-08-03 00:40:00.657', 192, null),
(70140, 3026, 2.03, '2012-08-03 00:40:00.673', 192, null),
(70141, 3025, 25.32, '2012-08-03 00:40:00.657', 192, null);

select TagID,
cast(TIMEandDATE as date) DATEOnly,
count(VALUE) VALUE_Count,
avg(VALUE) VALUE_Average
from @yourTable
where year(TIMEandDATE) = 2012
group by 
TagID, cast(TIMEandDATE as date)

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
you need to group by the date part and the tag
it's possible to group by multiple date parts and get subtotals via rollup

select TagId, Avg(Value) AvgValue, Count(Value) CountValue, 
  DatePart('year', TimeAndDate) as TdYear,
  Cast(TimeAndDate as Date) as TdDay
from YourTable
where TagId = 3006
group by rollup (TagId, Cast(TimeAndDate as Date), DatePart('year', TimeAndDate) )

Open in new window

1
Salad-DodgerInstrumentationAuthor Commented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@Salad-Dodger

yea, I was trying to demonstrate the sample with temporary table, which need to be defined with @yourTableName. cheers glad that help.
1
Geert GOracle dbaCommented:
that was the intended return.
rollup can produce more than 1 group by
0
Salad-DodgerInstrumentationAuthor Commented:
... I've soooo much to learn.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.