easy sql help please

can someone help me please... i'm a newbie to sql...

dbo.tbl_client
field names are q1_1, q1_2, q1_3, q1_4
where relat_to='manager

i need to find the average for these

i tried:
select sum(q1_1+q1_2+q1_3+q1_4/4) as average_q1
FROM dbo.tbl_client
WHERE relat_to='manager'

gave me a strange number!

also... how can i find the MAX for these answers? across multiple columns?

select MAX(??what goes here?) as q1_max
from dbo.tbl_client
where relat_to='manager'

thank you!!!!

michaelClassic order of operations, Michael. Division comes before addition, so your code is operating like this:
sum(q1_1+q1_2+q1_3+(q1_4/4))

You want this:
sum(q1_1+q1_2+q1_3+q1_4)/4

or maybe this
sum((q1_1+q1_2+q1_3+q1_4)/4)

(They may be equivalent. My brain is too fried to think about it this late in the day!)

You may get strange results if some of your columns contain nulls, or if your columns are integer values.

Not sure what you want by MAX. MAX value? MAX column value? Max average?

blindman|||um... okay... i REALLY did pass math... honest!

i was thinking that with all the damn time i put in front of this computer monitor that it would read my thoughts and KNOW what i wanted...

thanks for the syntax!

as to the Max value... i had multiple questions Q1_1, Q1_2, Q1_3,Q1_4

the answers for each can range from 0 to 7

the WHERE Clause is relat_to='manager' (next SQL will be relat_to='self' etc)

what i need is to know what the MAX score is for relat_to='manager' for all of the Q1 questions... so if a person is a 'manager' and their results for the questions were Q1_1=4, Q1_2=0,Q1_3=7, Q1_4=0 then the MAX should be "7" and (i'll swap out the MAX after and do the MIN too) the MIN will be "0"

please let me know if this makes sense and thank you for helping someone that you dont even know!!|||First create a new function:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

Then use this select statement:
Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman|||i'm obviously way over my head here.... i have no idea whatsoever how to create a function... i'm using Dreamweaver and its recordset interface and the database was an Access that was upsized.... i am committed to finsihing it this week and just may end up giving all this computer stuff up to becme a rodeo clown....

i'm searching now for how to create a function etc... i really do appreciate all you help!!! i'm just sorry that i'm not smart enough to implement it!!!!

michael|||You can just run this code against your database using Query Analyzer:

CREATE function dbo.LARGEST (@.value1 sql_variant, @.value2 sql_variant)
returns sql_variant as
--blindman, 8/03
--Returns the largest of two values
BEGIN
if @.value1 < @.value2 set @.value1 = @.value2
return @.value1
END

That will create your function. You can call your function and pass parameters just like you would call any other function, except that you must specify the owner of the function, "dbo". The code I gave should work if you cut and paste:

Select dbo.Largest(Q1_1, dbo.Largest(Q1_2, dbo.Largest(Q1_3,Q1_4)))

blindman

...by the way, switching from computer professional to rodeo clown is considered at best a lateral move. Aim for Ringmaster, or some other management role to further your career.sql