SQL Query to calculate weight or distribute weight based on value/null

Question

I have a table with columns - id, x1, x2, x3,x4

if i have values

1,y,y,y,y
2,y,null,n,null
3,y,null,null,null

then say, i want to calculate (1)/(number of y's) and display rows as

1,.25,.25,.25,.25
2,1,0,0,0
3,1,0,0,0

Is it possible to do this using sql query?

Answer 1

The naive version here would be

SELECT 
    id,
    CASE 
    WHEN x1 IS NULL THEN
        0
    WHEN f_eval(x1) + f_eval(x2,0) + f_eval(x3,0) + f_eval(x4,0) = 0 THEN
        -- default value
    ELSE
        1 / (f_eval(x1) + f_eval(x2,0) + f_eval(x3,0) + f_eval(x4,0))
    END,
    -- repeat for each column
FROM
    Table

where f_eval is defined as more or less the following:

CREATE FUNCTION f_eval(
      @x_value varchar(1)
BEGIN

    DECLARE @return_val int
    SELECT
        @return_val = 
        case
        WHEN ISNULL(@x_value, 'n') = 'y' THEN
            1
        ELSE
            0
        END

     RETURN @return_val
END

Answer 2

try the foillowing

select 

(SUM(case when x1 is null then 0 else 1 end))* 1.0/ ((SUM(case when x1 is null then 0 else 1 end) + SUM(case when x2 is null then 0 else 1 end)+ SUM(case when x3 is null then 0 else 1 end) + + SUM(case when x4 is null then 0 else 1 end)) * 1.0),
(SUM(case when x2 is null then 0 else 1 end))* 1.0/ ((SUM(case when x1 is null then 0 else 1 end) + SUM(case when x2 is null then 0 else 1 end)+ SUM(case when x3 is null then 0 else 1 end) + + SUM(case when x4 is null then 0 else 1 end)) * 1.0),
(SUM(case when x3 is null then 0 else 1 end))* 1.0/ ((SUM(case when x1 is null then 0 else 1 end) + SUM(case when x2 is null then 0 else 1 end)+ SUM(case when x3 is null then 0 else 1 end) + + SUM(case when x4 is null then 0 else 1 end)) * 1.0),
(SUM(case when x4 is null then 0 else 1 end))* 1.0/ ((SUM(case when x1 is null then 0 else 1 end) + SUM(case when x2 is null then 0 else 1 end)+ SUM(case when x3 is null then 0 else 1 end) + + SUM(case when x4 is null then 0 else 1 end)) * 1.0)
from table group by id

you might have make sure you dont divide by zero

Edit 2

by adding one more case to the denominator when 0 then 1. also you should change

   case when x1 is null then 0 else 1 end

to

 case when x1 = 'y' then 1 else 0 end

Answer 3

Here's another brute-force attack using a subquery. Setting up some test data:

CREATE TABLE MyTable
 (
   Id   int     not null
  ,Col1 char(1) null
  ,Col2 char(1) null
  ,Col3 char(1) null
  ,Col4 char(1) null
 )

INSERT MyTable
           select 1, 'Y', 'Y', 'Y', 'Y'
 union all select 2, 'Y', null, 'N', null
 union all select 3, 'Y', null, null, null
 union all select 4, null, null, null, null

I used character data for the target value, but you should have no problems making it numeric. I added a fourth line to check for divide by zeros (thanks ps!). So,

SELECT
   mt.ID
  ,case mt.Col1 when 'Y' then xx.distrib else 0 end
  ,case mt.Col2 when 'Y' then xx.distrib else 0 end
  ,case mt.Col3 when 'Y' then xx.distrib else 0 end
  ,case mt.Col4 when 'Y' then xx.distrib else 0 end
 from MyTable mt
  inner join (select
                ID
                ,1.0 / (  case Col1 when 'Y' then 1 else 0 end
                        + case Col2 when 'Y' then 1 else 0 end
                        + case Col3 when 'Y' then 1 else 0 end
                        + case Col4 when 'Y' then 1 else 0 end) distrib
               from MyTable) xx
   on xx.Id = mt.Id

appears to produce the desired results. (Run the subquery on its own and you get a divide by zero... but I honestly have no idea why running the whole query does not.

Answer 4

Try this

Sample data

declare  @t table( Id int,Col1 char(1) null,Col2 char(1) null,Col3 char(1) null,Col4 char(1) null)
INSERT @t select 1, 'Y', 'Y', 'Y', 'Y' union all select 2, 'Y', null, null, null
 union all select 3, 'Y', null, null, null

Query:

select t.id 
 ,case Col1 when 'Y' then LEFT(cast( 1.0/x.cntys as varchar(20)),4) else cast( 0 as varchar(1)) end x1
 ,case Col2 when 'Y' then LEFT(cast( 1.0/x.cntys as varchar(20)),4) else cast( 0 as varchar(1)) end x2
 ,case Col3 when 'Y' then LEFT(cast( 1.0/x.cntys as varchar(20)),4) else cast( 0 as varchar(1)) end x3
 ,case Col4 when 'Y' then LEFT(cast( 1.0/x.cntys as varchar(20)),4) else cast( 0 as varchar(1)) end x4

 from @t t
 join(
 select ROW_NUMBER() over(order by getdate()) rn, 
  COUNT(col1) + COUNT(col2) + COUNT(col3) + COUNT(col4) cntys from @t 
group by id)X
on t.Id = X.rn

Output:

id x1 x2 x3 x4 
1 0.25 0.25 0.25 0.25 
2 1.00 0 0 0 
3 1.00 0 0 0