• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1212

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

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
GTC-KTX
• 11
• 6
• 2
• +2
1 Solution

Commented:
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

Commented:
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

Commented:
Brandon,

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

Regards,

Patrick
0

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

Data EngineerCommented:

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

Commented:
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

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

Commented:
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 Commented:
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

Commented:
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

Commented:
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 Commented:
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

Data EngineerCommented:
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

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

Author Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:

"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
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.

Featured Post

• 11
• 6
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.