SQL Help - Dev Shed

Dev Shed Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

September 19th, 2003, 07:35 AM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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

September 19th, 2003, 10:26 AM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,720

Time spent in forums: 1 Month 3 Weeks 2 Days 18 h 11 m 16 sec
Reputation Power: 848

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/

September 19th, 2003, 12:41 PM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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...

September 19th, 2003, 12:44 PM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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....

September 19th, 2003, 03:11 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,720

Time spent in forums: 1 Month 3 Weeks 2 Days 18 h 11 m 16 sec
Reputation Power: 848

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

September 19th, 2003, 03:58 PM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

THANK YOU!!

i certainly appreciate your help!!!

a big thank you from a fellow Canadian!!

September 23rd, 2003, 02:59 PM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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

September 23rd, 2003, 05:54 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,720

Time spent in forums: 1 Month 3 Weeks 2 Days 18 h 11 m 16 sec
Reputation Power: 848

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

September 23rd, 2003, 07:24 PM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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!

#10

September 23rd, 2003, 07:42 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,720

Time spent in forums: 1 Month 3 Weeks 2 Days 18 h 11 m 16 sec
Reputation Power: 848

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

#11

September 24th, 2003, 07:02 AM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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???

#12

September 24th, 2003, 07:07 AM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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

#13

September 24th, 2003, 08:07 AM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation 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

#14

September 24th, 2003, 08:23 AM

michaelmcc

Junior Member

Join Date: Mar 2003

Posts: 19

Time spent in forums: < 1 sec
Reputation Power: 0

and when i change the syntax to include only 3 questions... the answer is 3 digits and not 4....

#15

September 24th, 2003, 11:15 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,720

Time spent in forums: 1 Month 3 Weeks 2 Days 18 h 11 m 16 sec
Reputation Power: 848

if any of them are null, that will make the SUM() null

rudy