Solved

# SQL query - How do i return Max, Min, Avg by date?

Posted on 2009-02-16
1,182 Views
I have a SQL table called "Weather" which is in the following format (see below):
Given a date range,  i would like to query the max temp, min temp, and average temp.
So for each date in the range, i would have 3 values, the max min and average.
This is a more complicated query than i am used to, so any help would be greatly appreciated

observation_date      station_id      tempF
1/1/2002 5:51      KPIT      16.0
1/1/2002 6:51      KPIT      15.1
1/1/2002 7:51      KPIT      14.0
1/1/2002 8:51      KPIT      14.0
1/1/2002 9:51      KPIT      12.0
1/1/2002 10:51      KPIT      12.0
1/1/2002 11:51      KPIT      10.0
1/1/2002 12:51      KPIT      10.0
1/1/2002 13:51      KPIT      12.0
1/1/2002 14:51      KPIT      15.1
1/1/2002 15:51      KPIT      18.0
1/1/2002 16:51      KPIT      19.9
1/1/2002 17:51      KPIT      21.0
1/1/2002 18:51      KPIT      21.9
1/1/2002 19:41      KPIT      23.0
1/1/2002 19:51      KPIT      21.9
1/1/2002 20:51      KPIT      23.0
1/1/2002 21:51      KPIT      21.9
1/1/2002 22:51      KPIT      19.9
1/1/2002 23:51      KPIT      19.0
1/2/2002 0:51      KPIT      17.1
1/2/2002 1:51      KPIT      15.1
1/2/2002 2:51      KPIT      16.0
0
Question by:GTC-KTX

LVL 6

Expert Comment

This should do it:
select distinct observation_date, max(tempF), min(TempF, avg(TempF) from weather
where observation_date >=date_range_start  and observation_date <= date_range_end
group by observation_date
order by observation_date

0

LVL 39

Expert Comment

select dateadd(d, datediff(d, 0, observation_date),0), min(TempF),max(TempF),avg(TempF)
from YourTable
group by dateadd(d, datediff(d, 0, observation_date),0)

If you want to group by Station_ID as well...

select dateadd(d, datediff(d, 0, observation_date),0), station_id, min(TempF),max(TempF),avg(TempF)
from YourTable
group by dateadd(d, datediff(d, 0, observation_date),0),station_id
0

LVL 92

Expert Comment

Brandon,

Neat trick with the dates there--I have always done the round trip CONVERT expression myself :)

Regards,

Patrick
0

LVL 39

Expert Comment

I used to do it that way too.  I like this way better :)
0

LVL 40

Expert Comment

If you want to get rid of 00:00:00.000 as in Brandon's solution, you can go for CONVERT. check this one.
``````select convert(varchar(10),observation_date,101) observation_date,min(TempF) Min_TempF,max(TempF) MAx_TempF,avg(TempF) Avg_TempF

from YourTable

group by convert(varchar(10),observation_date,101)
``````
0

LVL 39

Expert Comment

Yes you can do that.  But then it leaves you with varchar values and not datetime values.  Sorting, retrieving into an application or joining the results to tables on datetime values will result in a lot of implicit conversions (if it works).  Plus, you chose a format that is ambiguous based upon regional settings.

If you choose to NOT display the time, I would suggest format 112 which is non-ambiguous.

``````select convert(char(8),observation_date,112) observation_date,min(TempF) Min_TempF,max(TempF) MAx_TempF,avg(TempF) Avg_TempF

from YourTable

group by convert(char(8),observation_date,112)
``````
0

LVL 6

Expert Comment

Brandon
How does your solution support dynamic date range(If I need the values for a particular date range only)?
0

LVL 39

Expert Comment

I forgot to add that.  I missed that in the question.
``````--This is written to include values on the @StartDate and less than the @EndDate (plus all dates between).

select dateadd(d, datediff(d, 0, observation_date),0), station_id, min(TempF),max(TempF),avg(TempF)

from YourTable

where observation_date >= @StartDate

and observation_date < @EndDate

group by dateadd(d, datediff(d, 0, observation_date),0),station_id

--This is written to include values on the @StartDate and @EndDate (plus all dates between) by adding +1 end endDate and keeping <.

select dateadd(d, datediff(d, 0, observation_date),0), station_id, min(TempF),max(TempF),avg(TempF)

from YourTable

where observation_date >= @StartDate

and observation_date < @EndDate+1

group by dateadd(d, datediff(d, 0, observation_date),0),station_id
``````
0

Author Comment

brandon and iparul,
thanks for the help.  based on your comments i'm very close to getting this to work.  it works if i only try max and min.  however when i add the average i get the following error;

operand data type varchar is invalid for avg operator

it works for max and min whenever i delete this part of the code;

avg(TempF)

any ideas why this is happening?

thanks
0

LVL 39

Expert Comment

you are storing TempF in a varchar field.  Which also means that your min/max may be wrong.

this will fix it.
``````select observation_date, station_id, min(TempF),max(TempF),avg(TempF)

from

(select dateadd(d, datediff(d, 0, observation_date),0) as observation_date

, station_id

, cast(TempF as numeric(6,2) as TempF

from YourTable where observation_date >= @StartDate and observation_date < @EndDate+1

) a

group by observation_date,station_id
``````
0

LVL 39

Expert Comment

the above is written to include values on the @StartDate and @EndDate (plus all dates between) by adding +1 end endDate and keeping <.

The below is written to include values on the @StartDate and less than the @EndDate (plus all dates between).  The difference is <= or < on @EndDate

``````select observation_date, station_id, min(TempF),max(TempF),avg(TempF)

from

(select dateadd(d, datediff(d, 0, observation_date),0) as observation_date

, station_id

, cast(TempF as numeric(6,2) as TempF

from YourTable where observation_date >= @StartDate and observation_date < @EndDate

) a

group by observation_date,station_id
``````
0

Author Comment

brandon,
i'm trying to implement your last comment.  i keep getting the error;
incorrect syntax near the keyword as

do you have any suggestions?
0

LVL 40

Expert Comment

corrected the syntax error in brandon's solution
``````select observation_date, station_id, min(TempF),max(TempF),avg(TempF)

from

(select dateadd(d, datediff(d, 0, observation_date),0) as observation_date

, station_id

, cast(TempF as numeric(6,2)) as TempF

from YourTable where observation_date >= @StartDate and observation_date < @EndDate

) a

group by observation_date,station_id
``````
0

LVL 39

Expert Comment

doh.... missing ). Sorry GTC... Thanks Shar!
0

Author Comment

Thanks guys.......that got rid of that particular error, but revealed another.  I am now getting:
Conversion failed when converting datetime from character string

0

LVL 39

Expert Comment

Is observation_date also stored as a varchar?  If it's always in the above format, then use this.
``````select observation_date, station_id, min(TempF),max(TempF),avg(TempF)

from

(select dateadd(d, datediff(d, 0, observation_date),0) as observation_date

, station_id

, cast(TempF as numeric(6,2)) as TempF

from YourTable where convert(datetime, observation_date, 101) >= @StartDate and observation_date < @EndDate

) a

group by observation_date,station_id
``````
0

Author Comment

Brandon,
I am still getting that same error:

Conversion failed when converting datetime from character string

Is there something else i can provide that may help resolve?

Greg
0

LVL 39

Expert Comment

I only updated one location.  This SHOULD fix it as long as your datetime values are ALWAYS in the format above.
``````select observation_date, station_id, min(TempF),max(TempF),avg(TempF)

from

(select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as observation_date

, station_id

, cast(TempF as numeric(6,2)) as TempF

from YourTable where convert(datetime, observation_date, 101) >= @StartDate and observation_date < @EndDate

) a

group by observation_date,station_id
``````
0

Author Comment

Brandon,
Same error.  Should i replace every occurance, or just the two that you did?
I've attached my actual code snippet.  Maybe that will help.

``````sqlstring = "select observation_date, station_id, min(TempF),max(TempF),avg(TempF)from(select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as observation_date, station_id, cast(TempF as numeric(6,2)) as TempF from weather where convert(datetime, observation_date, 101) >= '& date_range_start &' and observation_date < '& date_range_end + 1 &' )a group by observation_date,station_id"
``````
0

LVL 39

Accepted Solution

You aren't actually capturing date_range_end from your code because you have

'& date_range_start &'
'& date_range_start &'

and you need:

'" & date_range_start & "'
'" & date_range_end + 1 & "'

You need to break out of the ssqlString = <DOUBLEQUOTE> with a <DOUBLEQUOTE> in order to get date_Range_start/end.

Try this:
``````sqlstring = "select observation_date, station_id, min(TempF),max(TempF),avg(TempF)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as observation_date, station_id, cast(TempF as numeric(6,2)) as TempF from weather where convert(datetime, observation_date, 101) >= '" & date_range_start & "' and convert(datetime, observation_date, 101) < '" & date_range_end + 1 & "' )a group by observation_date,station_id"
``````
0

Author Closing Comment

Brandon,
Thanks for sticking with me on that one!
One final question/confirmation about the code:
I made the user input station_id one of the conditions along with the date range.  So now, only the requested station id shows up.
My thought was that i should delete the "group by station_id" at the very end of the code since it now irrelevant (ie: only one station at a time will be coming through), but that gives an error.  It works fine the way it is, just trying to understand why i can't simply delete that condition.
Thanks again!
0

LVL 39

Expert Comment

"One final question/confirmation about the code:
I made the user input station_id one of the conditions along with the date range. So now, only the requested station id shows up.
My thought was that i should delete the "group by station_id" at the very end of the code since it now irrelevant (ie: only one station at a time will be coming through), but that gives an error. It works fine the way it is, just trying to understand why i can't simply delete that condition.
Thanks again!"

You can delete the "group by station" but you also have to delete the station_id from the select list.  Other wise you will get an error about station_id not being aggregated.
0

## Featured Post

A short article about a problem I had getting the GPS LocationListener working.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.