CODEWITHSUNDEEP
sqlaverage
fondue222
fondue222

Reputation: 25

SQL- Avg value by date and ID

I'm very new to SQL and am having trouble figuring out how to find some average values. Basically I have table with 3 columns, Date, ID, and Value. I'm trying to find the average for each ID on a daily basis. The actual table has thousands of entries with a varrying number of values logged for each ID on each day. Sample table below- 

SAMPLE TABLE
Date          ID     Value
1-1-14 12:45   1      5
1-1-14 13:45   1      6
1-1-14 08:45   2      8
1-1-14 10:45   2      8
1-2-14 01:45   1      2
1-2-14 04:45   1      4
1-2-14 01:45   2      20
1-2-14 04:45   2      24

SAMPLE RESULTS
Date       ID     AvgValue
1-1-14     1       5.5
1-1-14     2       8
1-2-14     1       3
1-2-14     2       22

I would greatly appreciate any help! Thanks!

Upvotes: 1

Views: 9264

Answers (1)

jpw
jpw

Reputation: 44871

The basic query is simple:

select date, id, avg(value)
from your_table
group by date, id, avg

However, as you just want the date part of the datetime column you can cast/convert that to a narrower date type, and also, if the value that you're averaging is an int you might have to cast it to a floating point type first. For SQL Server the query could look like this:

select 
  cast(date as date) as date, 
  id,   
  avg(cast(value as decimal(10,5))) as avg
from table1
group by cast(date as date), id
order by 1

Sample SQL Fiddle

Upvotes: 3

Related Questions