Interesting SELECT question

Hello
Does ANSI SQL support conditional selects ?
I have a table with say 15 fields
tblTale1
ID
F1
F2
F3
F4
F5
F6
..
..
..
F15
What I want to do is, add all the Fields from F1 to F15 and divide that
total by the total number of fields which were more than 0.
I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
number of fields which is more than 0 ?
Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still doesn't
give me the # of feilds for that one record which is greater than zero...
I can't think of any solution except to put these fields in another table.
Let me know if i'm missing something.
Thanks
I don't understand you. Can you post DDL+ sample data + expected result?
Did you mean ROWS not FIELDS?
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>
|||Your table design is very questionable but assuming no NULLs and no
negatives, try this:
SELECT
(f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9
+ f10 + f11 + f12 + f13 + f14 + f15)
/
(SIGN(f1) + SIGN(f2) + SIGN(f3) + SIGN(f4) + SIGN(f5)
+ SIGN(f6) + SIGN(f7) + SIGN(f8) + SIGN(f9) + SIGN(f10)
+ SIGN(f11) + SIGN(f12) + SIGN(f13) + SIGN(f14) + SIGN(f15))
FROM Table1 ;
If you have to cope with negatives then change SIGN(x) to ABS(SIGN(x)).
In general for conditional execution take a look at the CASE
expression.
David Portas
SQL Server MVP
|||you could use case
select (f1 + f2 + f3 + f4) / case when f1 = 0 and f2 = 0 and f3 = 0 and f4 =
0 then 1 else case when f1 = 0 then 0 else 1 end + case when f2 = 0 then 0
else 1 end + case when f3 = 0 then 0 else 1 end + case when f4 = 0 then 0
else 1 end end from tableName
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>
|||Here are a couple of options:
select id,
(f1*s1 + f2*s2 + f3*s3 + ... + f15*s15)
/ (s1 + s2 + s3 + ... + s15) as avgpos
from (select *,
1+sign(sign(f1) -1) as s1,
1+sign(sign(f2) -1) as s2,
1+sign(sign(f3) -1) as s3,
...
1+sign(sign(f15)-1) as s15
from (select id,
isnull(f1, 0) as f1,
isnull(f2, 0) as f2,
isnull(f3, 0) as f3,
...
isnull(f15, 0) as f15
from t1) as d1) as d2
select id, avg(val) as avgpos
from (select id, n,
case n
when 1 then f1
when 2 then f2
when 3 then f3
...
when 15 then f15
end as val
from t1,
(select 1 as n
union all select 2
union all select 3
...
union all select 15) as nums) as d
where val > 0
group by id
BG, SQL Server MVP
www.SolidQualityLearning.com
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
> doesn't give me the # of feilds for that one record which is greater than
> zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>
sql