April 30, 2012 at 3:50 pm
Below is an example table. I'm trying to get the average admission_exam_score, which happens to be char. I've tried CASTing it, but can't seem to get it right. Any thoughts?
/*Create the table*/
CREATE TABLE Admission_Exam_Test
(
Student_IDCHAR(15)NOT NULL,
Admission_ExamCHAR(25)NOT NULL,
Admission_Exam_ScoreCHAR(8)NOT NULL
)
;
/*Putting data in the table to create the test environment*/
INSERT INTO Admission_Exam_Test
(
Student_ID,
Admission_Exam,
Admission_Exam_Score
)
/*And some values...*/
Values
('240043', 'MCAT Biology Score', '25'),
('240043', 'MCAT Total', '18'),
('240043', 'Cum GPA', '3.45'),
('440643', 'MCAT Biology Score', '14'),
('440643', 'MCAT Total', '26'),
('440643', 'Sci GPA', '3.96')
April 30, 2012 at 3:56 pm
This works for me
select Admission_Exam , AVG(cast( Admission_Exam_Score as float))
from Admission_Exam_Test
group by Admission_Exam
Wat error are you gettig?
April 30, 2012 at 4:04 pm
Until we see the error you're getting this is hard to troubleshoot, however, you might want to take a look at the column and WHERE test the ISNUMERIC(Academic_Score) = 0 and see if you get any hits. Yes, there can be false positives in there (7e2 for example) but you'll find 99% of the issues.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 1, 2012 at 12:31 am
May 1, 2012 at 1:11 pm
Thanks for the replies. Here is the error I'm receiving
Error converting data type varchar to numeric.
Here is the actual query, which is at the end of a few cte queries: (The below statement won't work on the above table. It's so hard to recreate this scenario because I feel like some of the data I'm querying is of different datatypes, and perhaps that's causing the error. I've searched for NULLs and letters, but haven't found any so far. One thing I should have changed in my test example - the actual datatype is varchar(200))
SELECT
Person.Student_Last_Name
,Person.Student_First_Name
,cte_summary.Student_ID_2
,Person.Program_Version_Code
,Person.School_Status
,cte_summary.Exam_ID
,cte_summary.Admission_Exam
,AVG(CAST(cte_summary.Admission_Exam_Score AS DECIMAL(8,2)))
,cte_summary.Admission_Exam_Date
FROM Enrollment JOIN
cte_summary ON cte_summary.Student_ID_2 = Person.Student_ID
GROUP BY
Person.Student_Last_Name
,Person.Student_First_Name
,cte_summary.Student_ID_2
,Person.Program_Version_Code
,Person.School_Status
,cte_summary.Exam_ID
,cte_summary.Admission_Exam
,cte_summary.Admission_Exam_Date
May 1, 2012 at 1:32 pm
Evil Kraig F (4/30/2012)
Until we see the error you're getting this is hard to troubleshoot, however, you might want to take a look at the column and WHERE test the ISNUMERIC(Academic_Score) = 0 and see if you get any hits. Yes, there can be false positives in there (7e2 for example) but you'll find 99% of the issues.
Wow. That nailed it. I found an entry from years ago of '19L' 🙂 Thanks so much. One question - ISNUMERIC is a number checker, correct? So what would the "= 0" produce in this situation. Obviously it found the problem, but I'm not sure how.
May 1, 2012 at 3:50 pm
DataAnalyst110 (5/1/2012)
Evil Kraig F (4/30/2012)
Until we see the error you're getting this is hard to troubleshoot, however, you might want to take a look at the column and WHERE test the ISNUMERIC(Academic_Score) = 0 and see if you get any hits. Yes, there can be false positives in there (7e2 for example) but you'll find 99% of the issues.Wow. That nailed it. I found an entry from years ago of '19L' 🙂 Thanks so much. One question - ISNUMERIC is a number checker, correct? So what would the "= 0" produce in this situation. Obviously it found the problem, but I'm not sure how.
My pleasure, that's usually how it happens. ISNUMERIC checks to see if text is in a valid format for ANY numeric possibility. Be that percentile, float, has the YEN symbol for money, etc. All of those are valid, so you can get false positives when you're not expecting exponential values, for example.
If you lookup the function, you'll find that 1/true and 0/false are the only two results it provides per check. You tested the value and found it to be false on a numeric-conversion check. Make sense?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 1, 2012 at 3:56 pm
If you lookup the function, you'll find that 1/true and 0/false are the only two results it provides per check. You tested the value and found it to be false on a numeric-conversion check. Make sense?
Try running this to demonstrate the above:
select ISNUMERIC('Tandoori chicken') [NotNumeric], ISNUMERIC(55.99999) [Numeric]
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 2, 2012 at 3:15 pm
Phil Parkin (5/1/2012)
select ISNUMERIC('Tandoori chicken') [NotNumeric], ISNUMERIC(55.99999) [Numeric]
I LOVE 'Tandoori chicken' :w00t:
May 2, 2012 at 3:48 pm
Thanks for the explanation and example. Another of the many purposes of chicken... 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply