1

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!

dugas
  • 12,025
  • 3
  • 45
  • 51
fondue222
  • 25
  • 1
  • 4
  • 2
    Possible duplicate (and I am sure there are many others) - http://stackoverflow.com/questions/3100921/average-of-grouped-rows-in-sql-server – dugas Oct 13 '14 at 21:12
  • Thanks for the extra resource! I searched for a long time before deciding to post. It's hard to find answers to questions without using the right phrasing in the search bar. I do appreciate the link and will use it as well to help me solve my problem. – fondue222 Oct 13 '14 at 22:24

1 Answers1

3

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

jpw
  • 44,361
  • 6
  • 66
  • 86
  • I believe I'm doing something wrong, when I use either query you provided I get back multiple values for each date on each ID. I used CAST on the date and value fields but it doesn't seem to work. – fondue222 Oct 14 '14 at 13:06
  • @fondue222 What database are you using? If you get more than one value per date it's because the grouping is happening at the full datetime level and not just the date. Maybe using `cast(date as date)` isn't the correct way for your particular database (with mysql and postgresql it's most likely another cast or convert that's needed. – jpw Oct 14 '14 at 13:49