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

x
?
Solved

SQL Query - min, max, average - excluding NA values

Posted on 2009-02-19
6
Medium Priority
?
1,412 Views
Last Modified: 2012-05-06
Worked with Brandon on this before, so hopefully he or someone else might be able to help.
The SQL query below occasionally gives me the following error:
(Error converting data type varchar to numeric)
This is due to the fact that, occasionally, the tempF field is populated with NA instead of a value.  This occurs when the temperature is not available at the time we sample it, due to station being down, or internet being down etc.
I can either change the SQL string or i can change the database to populate something other than NA whenever it can't get the temp.  Any suggestions would be appreciated!
sqlstring1 = "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
 (station_id = '" & querywthr1 & "') and 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 order by observation_date"

Open in new window

0
Comment
Question by:GTC-KTX
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
jparul earned 1000 total points
ID: 23683294
Try this: Added a condition in where clause to skip the cases where tempF = NA
sqlstring1 = "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  (tempF <> 'NA' and station_id = '" & querywthr1 & "') and 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 order by observation_date"
 
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23684787
Replace every TempF with

case when isnumeric(TempF )<>1 then NULL else TempF end

0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1000 total points
ID: 23684801
Here:
sqlstring1 = "select observation_date, station_id, min(case when isnumeric(TempF )<>1 then NULL else TempF end),max(case when isnumeric(TempF )<>1 then NULL else TempF end),avg(case when isnumeric(TempF )<>1 then NULL else TempF end)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as 
observation_date, station_id, cast(case when isnumeric(TempF )<>1 then NULL else TempF end as numeric(6,2)) as TempF from weather where
 (station_id = '" & querywthr1 & "') and 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 order by observation_date"

Open in new window

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:GTC-KTX
ID: 23685617
Thanks guys!  both good workable solutions.  I will award points to both.
One follow up though that came to mind when looking at your solutions.
Is there a way to flag (on my output query) when i come across a date that had NA's for the temp?
I see that your solution will exclude them in the calculation of min, max, and everage.  But from a data integrity standpoint it would be good to know which days had NA's.  I don't know what would be the easiest way to flag.  Right now the output is 5 columns.....Observation_Date, Station_id, min, max, average.  Maybe there could be a sixth column that is populated with a flag everytime there are NA's in the temp calculation for a certain date.

By the way, if this is something more involved that should be asked as a new question with new points, just let me know and i can do that.

Greg
0
 
LVL 6

Expert Comment

by:jparul
ID: 23686406
You can run a separate query like this to get that information:
select observation_date, station_id, tempF from weather where tempF = 'NA'
0
 

Author Comment

by:GTC-KTX
ID: 23686588
Thanks iparul!  i guess i didn't consider a completely separate query.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question