Hi,
I have a table like below and i am trying to come up with last two columns Den and Num where Den is the sum of the all columns Itm1 through Itm6 only if the value in each column is >0 and similarly the column Num is the sum of columns Itm1 through Item6 only where value in column is 1.
Any help will be greatly appreciated.
Thanks,
act Itm1 Itm2 Itm3 Itm4 Itm5 Itm6 Den Num
a 1 -1 1 1 1 1 5 5
b 1 2 1 1 1 1 7 5
c 1 1 1 1 1 1 6 6
d 1 1 1 1 1 1 6 6
e 1 1 1 1 1 1 6 6
f 2 1 1 1 1 1 7 5
g 1 1 1 1 1 1 6 6
h 1 1 1 1 1 1 6 6
i 1 1 1 1 1 1 6 6
j 1 1 1 1 1 1 6 6
Basic idea...
Select t.act
, t.itm1
, t.itm2
, t.itm3
, t.itm4
, t.itm5
, t.itm6
, Den = (Select sum(val)
From (
Values (t.Itm1)
, (t.Itm2)
, (t.Itm3)
, (t.Itm4)
, (t.Itm5)
, (t.Itm6)) As den(val)
Where den.val > 0)
, Num = (Select sum(val)
From (
Values (t.Itm1)
, (t.Itm2)
, (t.Itm3)
, (t.Itm4)
, (t.Itm5)
, (t.Itm6)) As num(val)
Where num.val = 1)
From dbo.YourTable t
This should get you started...
Thank you for the quick response. I will try it.
Thanks a lot jeffW it worked great. Now i have an other challenge where i need to add some columns where there values are greater than zero and some columns whose value is is 1.
any suggestions.
Thanks
Would need a lot more information to understand what you are requesting - maybe a new post for that issue?
HI,
i have a table with columns a_1, a_2, a_9, a_11, a_12 and a_10.
i will like to create an additional column which is sum of columns based on their values ( as below formula which works in a proprietary software but not in sql).
Basically it will add column a_1 where value of a_1 is greater than zero with a_2 where vaule of a_2 is greater than zero with a_3 where value of a_3>0 with sum of a_11 and a_12 whose values are >0 and product of a_10 <=1.
Any help is greatly appreciated.
(a_1 > 0) + (a_2 > 0) + (a_9 > 0) + (((a_11 > 0) + (a_12 > 0)) * (a_10 <= 1))
This should have been in a new topic, but try this:
select case when a_1<0 then 0 else a_1 end
+case when a_2<0 then 0 else a_2 end
+case when a_9<0 then 0 else a_9 end
+((case when a_11<0 then 0 else a_11 end
+case when a_12<0 then 0 else a_12 end
)
*case when a_10>1 then 0 else a_10 end
)
from yourtable
;