An uneccessarily lengthy query 1

[jimny]

There must be an easier way to get the same rsults from this lengthy query. I am trying to return the average of each column excluding rows that contain the value '0'. this is on ASP page, but the SQl is more my issue.

strSQL = "SELECT count(*) as Rows,sum(q1) as sq1,sum(q2) as sq2,sum(q3) as sq3" & _
",sum(q4) as sq4,sum(q5) as sq5,sum(q6) as sq6,sum(q7) as sq7,sum(q8) as sq8" & _
",sum(q9) as sq9,sum(q10) as sq10 FROM CofE03"
set rs = server.CreateObject("ADODB.RecordSet&quot
set rs = conn.Execute(strSQL)
dim RwCnt,sQ1,sQ2,sQ3,sQ4,sQ5,sQ6,sQ7,sQ8,sQ9,sQ10
Rwcnt = rs("Rows&quot
sQ1 = rs("sq1&quot
sQ2 = rs("sq2&quot
sQ3 = rs("sq3&quot
sQ4 = rs("sq4&quot
sQ5 = rs("sq5&quot
sQ6 = rs("sq6&quot
sQ7 = rs("sq7&quot
sQ8 = rs("sq8&quot
sQ9 = rs("sq9&quot
sQ10 = rs("sq10&quot
rs.Close
set rs = nothing
dim z1,z2,z3,z4,z5,z6,z7,z8,z9,z10
set rs = server.CreateObject("ADODB.RecordSet&quot
strSQl = "SELECT count(q1) as z1 FROM CofE03 WHERE q1 = '0'"
set rs = conn.Execute(strSQL)
z1 = rs("z1&quot
strSQl = "SELECT count(q2) as z2 FROM CofE03 WHERE q2 = '0'"
set rs = conn.Execute(strSQL)
z2 = rs("z2&quot
strSQl = "SELECT count(q3) as z3 FROM CofE03 WHERE q3 = '0'"
set rs = conn.Execute(strSQL)
z3 = rs("z3&quot
strSQl = "SELECT count(q4) as z4 FROM CofE03 WHERE q4 = '0'"
set rs = conn.Execute(strSQL)
z4 = rs("z4&quot
strSQl = "SELECT count(q5) as z5 FROM CofE03 WHERE q5 = '0'"
set rs = conn.Execute(strSQL)
z5 = rs("z5&quot
strSQl = "SELECT count(q6) as z6 FROM CofE03 WHERE q6 = '0'"
set rs = conn.Execute(strSQL)
z6 = rs("z6&quot
strSQl = "SELECT count(q7) as z7 FROM CofE03 WHERE q7 = '0'"
set rs = conn.Execute(strSQL)
z7 = rs("z7&quot
strSQl = "SELECT count(q8) as z8 FROM CofE03 WHERE q8 = '0'"
set rs = conn.Execute(strSQL)
z8 = rs("z8&quot
strSQl = "SELECT count(q9) as z9 FROM CofE03 WHERE q9 = '0'"
set rs = conn.Execute(strSQL)
z9 = rs("z9&quot
strSQl = "SELECT count(q10) as z10 FROM CofE03 WHERE q10 = '0'"
set rs = conn.Execute(strSQL)
z10 = rs("z10&quot
tQ1 = Round(sq1/(RwCnt - z1),2)
tQ2 = Round(sq2/(RwCnt - z2),2)
tQ3 = Round(sq3/(RwCnt - z3),2)
tQ4 = Round(sq4/(RwCnt - z4),2)
tQ5 = Round(sq5/(RwCnt - z5),2)
tQ6 = Round(sq6/(RwCnt - z6),2)
tQ7 = Round(sq7/(RwCnt - z7),2)
tQ8 = Round(sq8/(RwCnt - z8),2)
tQ9 = Round(sq9/(RwCnt - z9),2)
tQ10 = Round(sq10/(RwCnt - z10),2)

 

[r937]

you can do it all in one query (runs faster)

select case when sum(
case when q1=0 then 0 else 1 end
) = 0
then 0
else sum(q1) / sum(
case when q1=0 then 0 else 1 end
)
end as avg1

repeat this pattern for the other columns

the "count" is a sum of 1's and 0's

when the "count" is 0, the average is set to 0 (to prevent dividing by 0), otherwise, the sum is divided the "count"


rudy

 

[jimny]

Thanx Rudy