June 17, 2003 at 1:39 pm
Hello,
I have a table called 'tblRCStudentGrades' in which there are four VarChar(2)fields that can contain these values: 1-5, NULL, 'N'.
I need to arive at a single average of the values in these combined four fields where the number used in the 'AVG' calculation will change based on the number of fields that contain a value of 1-5. If none of the fields contain a number of 1-5, then I don't want to compute the average of the fields. The field values to be averaged will be selected where a field called permnum = a value.
I've been able to get as far as handling the fields when they have certain values with the following code:
*************************************
Select
CASEWhen ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint)Else Null End AS intST1Q1,
CASEWhen ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint)Else Null End AS intST1Q2,
CASEWhen ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint)Else Null End AS intST1Q3,
CASEWhen ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint)Else Null End AS intST1Q4
From tblRCStudentGrades
where Permnum = '602'
*************************************
To clarify this a bit, I have some possible scenarios below that would be examples of situations I would need to account for:
When:
ST1Q1 = 2
ST1Q2 = 'N'
ST1Q3 = 2
ST1Q4 = 5
Then: (2+2+5)/3
When:
ST1Q1 = 2
ST1Q2 = 'N'
ST1Q3 = NULL
ST1Q4 = 5
Then: (2+5)/2
When:
ST1Q1 = 'N'
ST1Q2 = 'N'
ST1Q3 = NULL
ST1Q4 = 'N'
Then: (Do not compute an average)
Please let me know if you need further clarification. If you have Ideas on this, please pass them along.
Thanks!
CSDunn
June 17, 2003 at 3:19 pm
May not be the best solution but should work for you calculation try
(
ISNULL((CASE When ST1Q1 = 'N' Then Null ELSE ST1Q1 End),0) +
ISNULL((CASE When ST1Q2 = 'N' Then Null ELSE ST1Q2 End),0) +
ISNULL((CASE When ST1Q3 = 'N' Then Null ELSE ST1Q3 End),0) +
ISNULL((CASE When ST1Q4 = 'N' Then Null ELSE ST1Q4 End),0)
) / (
(CASE When ST1Q1 = 'N' Then 0 ELSE 1 End) +
(CASE When ST1Q2 = 'N' Then 0 ELSE 1 End) +
(CASE When ST1Q3 = 'N' Then 0 ELSE 1 End) +
(CASE When ST1Q4 = 'N' Then 0 ELSE 1 End)
)
and to get rid of the rows that will be an issue like this one
When:
ST1Q1 = 'N'
ST1Q2 = 'N'
ST1Q3 = NULL
ST1Q4 = 'N'
Then: (Do not compute an average)
use a where clause like so
WHERE
ISNULL(ST1Q1,'N') != 'N' AND
ISNULL(ST1Q2,'N') != 'N' AND
ISNULL(ST1Q3,'N') != 'N' AND
ISNULL(ST1Q4,'N') != 'N'
I am sure it is along those lines but there may be a better way someone else can see. But should give you a start.
June 17, 2003 at 3:35 pm
I'll go with Antares686 suggestion,
thus version II
Select Calc.StudentNr,Calc.ColSum/Calc.FieldCnt From (
Select StudentNr,
(ISNULL((CASE When ST1Q1 = 'N' Then Null ELSE ST1Q1 End),0) +
ISNULL((CASE When ST1Q2 = 'N' Then Null ELSE ST1Q2 End),0) +
ISNULL((CASE When ST1Q3 = 'N' Then Null ELSE ST1Q3 End),0) +
ISNULL((CASE When ST1Q4 = 'N' Then Null ELSE ST1Q4 End),0)),
((CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End))
From tblRCStudentGrades) as Calc(StudentNr,ColSum,FieldCnt)
Where Calc.FieldCnt>0
June 17, 2003 at 4:11 pm
Version III - which slightly modifies 5409045121009's script to
- account for the varchar values
- add output for no average cases
- provide alternative methods of checking columns (I can't say if this is a better method)
Select Calc.StudentNr,Calc.ColSum,Calc.FieldCnt,
CASE WHEN Calc.FieldCnt = 0 THEN 'No Average'
ELSE Convert(Varchar(20),(Calc.ColSum/Calc.FieldCnt)) END AvgVal
From (
Select StudentNr,
(
(CASE When isNumeric(ST1Q1) = 0 Then 0 ELSE Convert(tinyint,ST1Q1) End) +
(CASE When isNumeric(ST1Q2) = 0 Then 0 ELSE Convert(tinyint,ST1Q2) End) +
(CASE When isNumeric(ST1Q3) = 0 Then 0 ELSE Convert(tinyint,ST1Q3) End) +
(CASE When isNumeric(ST1Q4) = 0 Then 0 ELSE Convert(tinyint,ST1Q4) End)
),
(
(CASE When ST1Q1 like '[1-5]' Then 1 ELSE 0 End) +
(CASE When ST1Q2 like '[1-5]' Then 1 ELSE 0 End) +
(CASE When ST1Q3 like '[1-5]' Then 1 ELSE 0 End) +
(CASE When ST1Q4 like '[1-5]' Then 1 ELSE 0 End)
)
From tblRCStudentGrades) as Calc(StudentNr,ColSum,FieldCnt)
June 17, 2003 at 5:31 pm
Hello,
The following is what I came up with, but the final average is still a little off:
*****************************************************************
Select
Permnum,
ROUND(
CAST
(
(
ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)
)
/
(
(CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)
)
AS Decimal(3,2)
),1)As Results
FROM tblRCStudentGrades
WHERE
Permnum = '602'
and
(ISNULL(ST1Q1,'N') <> 'N' or
ISNULL(ST1Q2,'N') <> 'N' or
ISNULL(ST1Q3,'N') <> 'N' or
ISNULL(ST1Q4,'N') <> 'N')
*******************************************************
In the case where ST1Q1=2, ST1Q4=5, and the other fields are null (or 'N'), the average should come out to 3.50, but I get 3.00.
Am I not using ROUND correctly to acheive the desired result?
Thanks Again!
June 17, 2003 at 5:43 pm
I think you need to shift the CAST down to the denominator...this is because it is already doing an integer division and rounding the result - and this rounded result is being converted to numeric
ROUND(
(
ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)
)
/
CAST(
(
(CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)
)As Decimal(3,2)
),1)As Results
June 17, 2003 at 6:53 pm
The problem is interger values produce integer results, at least one has to be a decimal to get a decimal result. Try
Select
Permnum,
ROUND(
(
ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +
ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)
)
/
CAST((
(CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +
(CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)
) AS Decimal(3,2)), 1) As Results
FROM
tblRCStudentGrades
WHERE
Permnum = '602' and
(
'N' NOT IN (
ISNULL(ST1Q1,'N'),
ISNULL(ST1Q2,'N'),
ISNULL(ST1Q3,'N'),
ISNULL(ST1Q4,'N')
)
)
Try the NOT IN where option I show above for OR as well, just using the IN standard. This is reverse of what most people think of but is quite usefull.
June 18, 2003 at 2:35 am
Amended original post to add permnum limitation.
declare @denom int
declare @num int
declare @aver float
declare myfetch cursor for select permnum, cola, colb, colc, cold from rmx
declare @myfetch nvarchar(10)
declare @myfetcha varchar(2)
declare @myfetchb varchar(2)
declare @myfetchc varchar(2)
declare @myfetchd varchar(2)
create table #mytemp (permnum nvarchar(10), average float)
open myfetch
fetch next from myfetch into @myfetch, @myfetcha, @myfetchb, @myfetchc, @myfetchd
while @@fetch_status = 0
begin
set @denom = casewhen isnumeric(@myfetcha)=1 then 1
else @denom
end
set @denom = casewhen isnumeric(@myfetchb)=1 then @denom+1
else @denom
end
set @denom = casewhen isnumeric(@myfetchc)=1 then @denom+1
else @denom
end
set @denom = casewhen isnumeric(@myfetchd)=1 then @denom+1
else @denom
end
set @num = casewhen isnumeric(@myfetcha)=1 then @myfetcha
else @num
end
set @num = casewhen isnumeric(@myfetchb)=1 then @num+@myfetchb
else @num
end
set @num = casewhen isnumeric(@myfetchc)=1 then @num+@myfetchc
else @num
end
set @num = casewhen isnumeric(@myfetchd)=1 then @num+@myfetchd
else @num
end
set @aver = casewhen @denom !=0 then cast(@num as float)/cast(@denom as float)
else null
end
insert into #mytemp (permnum, average)
values (@myfetch, @aver)
set @num = 0
set @denom = 0
fetch next from myfetch into @myfetch, @myfetcha, @myfetchb, @myfetchc, @myfetchd
end
close myfetch
deallocate myfetch
select * from #mytemp where permnum = '602'
drop table #mytemp
Edited by - Aadomm on 06/18/2003 03:16:36 AM
June 18, 2003 at 2:38 am
May not be practical or possible, but you could have a table in with the numeric scores for the various possible answers. Then you could join the tables on the response to get the numeric score for each answer.
June 18, 2003 at 9:59 am
Thanks to all, your input has been very helpful!
CSDunn
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply