CODEWITHSUNDEEP
sqlsql-server
RizJa
RizJa

Reputation: 2031

SQL - Get column value based on another column's average between select rows

I've got a table something like..

[DateValueField][Hour][Value]
 2014-09-01      1     200
 ...
 2014-09-01      24    400
 2014-09-02      1     220
 ...
 2014-09-02      24    200
 ...

I need the same value for each DateValueField based on the average Value for Hour between 6-12 for example but have that display for all hours, not just 6-12. For instance...

[DateValueField][Hour][Value]
 2014-09-01      1     300
 ...
 2014-09-01      24    300
 2014-09-02      1     190
 ...
 2014-09-02      24    190
 ...

Query I'm trying is...

select DateValueField, Hour,
(select avg(Value) as Value from MyTable where Hour
between 6 and 12) as Value from MyTable
where DateValueField between '2014' and '2015'
group by DateValueField, Hour
order by DateValueField, Hour

But it gives me the Value as an average of ALL Values but I need it averaged out for that particular day between the hours I specify.

I'd appreciate some help/advice. Thanks!

Upvotes: 2

Views: 2734

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

You can use a derived table to get the average value between hours 6 and 12 grouped by date and then join that to your original table

select t1.DateValueField, t1.Hour, t2.avg_value
from MyTable t1
join (
    select DateValueField, avg(Value) avg_value
    from MyTable
    where hour between 6 and 12
    group by DateValueField
) t2 on t2.DateValueField = t1.DateValueField
order by t1.DateValueField, t1.Hour

Note: You may want to use a left join if some of your dates don't have values between hours 6 and 12 but you still want to retrieve all rows from MyTable.

Upvotes: 1

Related Questions