[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1206
  • Last Modified:

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
Asked:
GTC-KTX
  • 11
  • 6
  • 2
  • +2
1 Solution
 
jparulCommented:
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
 
BrandonGalderisiCommented:
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
 
Patrick MatthewsCommented:
Brandon,

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

Regards,

Patrick
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
BrandonGalderisiCommented:
I used to do it that way too.  I like this way better :)
0
 
SharathData 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)

Open in new window

0
 
BrandonGalderisiCommented:
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)

Open in new window

0
 
jparulCommented:
Brandon
How does your solution support dynamic date range(If I need the values for a particular date range only)?
0
 
BrandonGalderisiCommented:
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

Open in new window

0
 
GTC-KTXAuthor 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
 
BrandonGalderisiCommented:
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

Open in new window

0
 
BrandonGalderisiCommented:
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

Open in new window

0
 
GTC-KTXAuthor 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
 
SharathData 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

Open in new window

0
 
BrandonGalderisiCommented:
doh.... missing ). Sorry GTC... Thanks Shar!
0
 
GTC-KTXAuthor 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
 
BrandonGalderisiCommented:
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

Open in new window

0
 
GTC-KTXAuthor 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?

Thanks for your continued help!
Greg
0
 
BrandonGalderisiCommented:
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

Open in new window

0
 
GTC-KTXAuthor 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"

Open in new window

0
 
BrandonGalderisiCommented:
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"

Open in new window

0
 
GTC-KTXAuthor 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
 
BrandonGalderisiCommented:
Posting grading comments to put context on follow up answer:

"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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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