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

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

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.
ThanksI 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
>
>

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.
ThanksI 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
>
>

integration of 2 SQL 2005 DB Servers

Hi,

I have 2 database servers and each has same database.Program that inserts these databases randomly selects one of them to insert data.So only difference is data.

I have to generate some report using these 2 sources.System administrators reject integrating these 2 servers into one.

My question is , I want to run a query that looks the column x in table y and finds unique records in this table.But this works just in one DB.What do you recommend me to find solution that finds real unique records, i.e. looks both servers to find records.

Is there a Microsoft product that may be configured brain of different DB Servers or any other solution?

I have also memory restrictions and data is considered as more than 100 GBytes for each server.

Answers will be highly appreciated.

Thanks.

sysdamins' guys also denied you create links between these servers?|||No, link is allowed.