CODEWITHSUNDEEP
sqlsql-serverlogic
ryan.kom
ryan.kom

Reputation: 187

Filter out rows from the final result, while still utilizing some of their values?

To give an example, let's say I have a view that returns the following result:

| id | foreignkey | value1 | value2 |
|----|------------|--------|--------|
| 1  | 500        | -100   | 0      |
| 2  | 500        | 900    | 15     |
| 3  | 500        | 570    | 25     |
| 4  | 999        | 100    | 57     |
| 5  | 999        | 150    | 0      |

The logic I'm trying to implement is as follows -

So in this example, I want the final result to be

| id | foreignkey | value1 | value2 |
|----|------------|--------|--------|
| 2  | 500        | 800    | 15     |
| 3  | 500        | 470    | 25     |
| 4  | 999        | 250    | 57     |

Any ideas? I was thinking something with group by might be possible but haven't been able to come up with a solution yet.

Upvotes: 0

Views: 61

Answers (3)

sacse
sacse

Reputation: 3779

One way is to treat all zero rows as one group and all others as another group (based on foreignkey) and then simply join and add the values and finally select only the required ones:

;with cte as
(
    select id, foreignkey, value1, value2,dense_rank() over (partition by foreignkey order by (case when value2 = 0 then 0 else 1 end)) as rn
    from @t t1
)
,cte2 as
(
    select t1.id, t1.foreignkey, t1.value1 + isnull(t2.value1,0) as value1, t1.value2
    from cte t1
    left join cte t2 on (t2.foreignkey = t1.foreignkey and t1.rn<> t2.rn)
)
select * from cte2
where value2 <> 0

Please find the db<>fiddle here.

Upvotes: 0

forpas
forpas

Reputation: 164174

With SUM() window function:

select id, foreignkey, value1 + coalesce(total, 0) value1, value2
from (
  select *, 
    sum(case when value2 = 0 then value1 end) over (partition by foreignkey) total
  from tablename
) t 
where value2 <> 0

See the demo.
Results:

> id | foreignkey | value1 | value2
> -: | ---------: | -----: | -----:
>  2 |        500 |    800 |     15
>  3 |        500 |    470 |     25
>  4 |        999 |    250 |     57

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Hmmm . . . assuming that this doesn't filter out all rows, you can use window functions like this:

select id, foreignkey, value1, value2 + (case when seqnum = 1 then value2_0 else 0 end)
from (select t.*,
             row_number() over (partition by foreignkey order by value1 desc) as seqnum,
             sum(case when value1 = 0 then value2 end) over (partition by foreignkey) as value2_0
      from t
     ) t
where value2 <> 0;

Upvotes: 0

Related Questions