Average each day over years

Salad-Dodger
Salad-Dodger used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul MacDonaldDirector, Information Systems

Commented:
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.
Salad-DodgerInstrumentation

Author

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...
Paul MacDonaldDirector, Information Systems

Commented:
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'
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Salad-DodgerInstrumentation

Author

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.
Software Team Lead
Commented:
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

Geert GOracle dba
Top Expert 2009
Commented:
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

Salad-DodgerInstrumentation

Author

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.
Ryan ChongSoftware Team Lead

Commented:
@Salad-Dodger

yea, I was trying to demonstrate the sample with temporary table, which need to be defined with @yourTableName. cheers glad that help.
Geert GOracle dba
Top Expert 2009

Commented:
that was the intended return.
rollup can produce more than 1 group by
Salad-DodgerInstrumentation

Author

Commented:
... I've soooo much to learn.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial