Thread: SQL Help

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0

    SQL Help


    i'm a newbie so forgive me since i'm a dolt...

    i have a table called "client"

    in "client" i have multiple fields named "q11" "q12""q13"q14""q15"
    these are numeric answers that range from 0 to 7

    i also have another field called "relat_to"

    i need to AVERAGE "q11" to "q15" based on the "RELAT_TO" field...

    please help!

    michael
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    what does based on the "RELAT_TO" field mean?

    and what kind of average? across the row? that's easy, it's (q11+q12+q13+q14+q15)/5

    or did you want averages for each of the q columns?

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0

    more info


    sorry....

    the "relat_to" field holds info like "manager", "self," "other" etc...

    what i would like to know is how many "0s" were given by a "manager" (relat_to='manager') for Q11, Q12, Q13, Q14...

    the table has a field for each question (q11 etc) and each question can be a number from 0 to 7...

    please let me know if there is any other info that will help me... and thanks...
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    and... ALL of the Question 1 answers should be considered as part of the SAME question..... so even though there are multiple questions (q11,q12 etc).. they need to be considered as a whole....
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    > AVERAGE "q11" to "q15" based on the "RELAT_TO" field...
    Code:
    select RELAT_TO
         , sum(q11+q12+q13+q14+q15)/5  as average_q11_q15
      from client
    group
        by RELAT_TO

    > how many "0s" were given by a "manager" for Q11, Q12, Q13, Q14...
    Code:
    select sum( case when q11=0 then 1 else 0 end
              + case when q12=0 then 1 else 0 end
              + case when q13=0 then 1 else 0 end
              + case when q14=0 then 1 else 0 end
              + case when q15=0 then 1 else 0 end )  as manager_zeroes
      from client
     where RELAT_TO = 'manager'
    rudy
    http://r937.com/
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0

    THANK YOU!!


    i certainly appreciate your help!!!

    a big thank you from a fellow Canadian!!
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    if i may be so bold.... how do i find the MIN and Max of multiple fileds?

    field names are q1_1, q1_2, q1_3,q1_4

    select relat_to
    , MAX(...?....) as q1_max
    from dbo.tbl_client
    where relat_to='manager'
    group by relat_to
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    ah, this is where you have to redesign your table

    otherwise, you need another humungous CASE construction.... when q1_1 > q1_2 and q1_2 > q1_3... and so on... something like 4*3*2*1= 24 different combinations...

    rudy
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    well.... i cannot redesign the table... but... is this what you meant? (and i dont mind cutting and pasting)

    Select
    IIF (MAX(q1_1) >= MAX(q1_2),
    IIF(MAX(q1_1) >= MAX(q1_3), MAX (q1_1),
    IIF (MAX(q1_2) >= MAX(q1_3), MAX(q1_2), MAX(q1_3)))) AS max_q1

    i'm not sure if that is correct... i found the syntax elsewhere... but do not know how to increase the numbers of q's to 5....

    thx for all your help!
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    alternatively, you could use a union in a derived table...
    Code:
    select max(q1) 
      from (
           select q1_1            as q1
             from dbo.tbl_client 
            where relat_to='manager' 
           union
           select q1_2 
             from dbo.tbl_client 
            where relat_to='manager' 
           union
           select q1_3 
             from dbo.tbl_client 
            where relat_to='manager' 
           union
           select q1_4 
             from dbo.tbl_client 
            where relat_to='manager' 
           union
           select q1_5 
             from dbo.tbl_client 
            where relat_to='manager' 
           ) as derivedtable
    sorry i didn't offer this before, it's perfectly okay in microsoft sql server, for some reason i misread "ms sql" as "mysql" as the name of this forum...

    rudy
  20. #11
  21. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    Hi Rudy,

    i am receiving some *hinky* responses from the AVERAGE statement above... my answer comes out to 3912... now i know that aint right!!!

    here is what i have:


    SELECT sum((q1_1+q1_2+q1_3+q1_4)/4) as average_q1
    FROM dbo.tbl_client
    WHERE relat_to='manager' AND group_name = 'MMColParam'
    group by relat_to

    have i done something wrong???
  22. #12
  23. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    sorry.... i just realized my error (fingers crossed)...

    SELECT relat_to
    ,sum((q1_1+q1_2+q1_3+q1_4)/4) as average_q1
    FROM dbo.tbl_client
    WHERE relat_to='manager' AND group_name = 'MMColParam'
    group by relat_to
  24. #13
  25. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    nope... and here is my exact syntax... i need some sleep.... i'm getting 3912 as my answer...

    SELECT relat_to, sum((q1_1+q1_2+q1_3+q1_4)/4) as average_q1
    FROM dbo.tbl_client
    WHERE relat_to='manager' AND group_name = 'MMColParam' group by relat_to
  26. #14
  27. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    19
    Rep Power
    0
    and when i change the syntax to include only 3 questions... the answer is 3 digits and not 4....
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    if any of them are null, that will make the SUM() null

    rudy
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo