Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: 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!!!!

    michael

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Classic 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

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    thank you....

    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!!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Sep 2003
    Posts
    3

    thanks!!!

    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •