• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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.
0
Salad-Dodger
Asked:
Salad-Dodger
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
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-DodgerAuthor 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Salad-DodgerAuthor 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 ChongCommented:
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
 
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-DodgerAuthor 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 ChongCommented:
@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-DodgerAuthor Commented:
... I've soooo much to learn.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now