How to get conditional SUM?

I am trying to get a conditional sum based on another column. For example, suppose I have this dataset:

ID  Date    Type    Total
-----------------------
5   12/16/2019  0   7
5   12/16/2019  1   0
5   12/17/2019  0   7
5   12/17/2019  1   7
5   12/18/2019  0   7
5   12/18/2019  1   0
5   12/19/2019  0   7
5   12/19/2019  1   7
5   12/20/2019  0   7
5   12/20/2019  1   7
5   12/23/2019  0   7
5   12/24/2019  0   7
5   12/25/2019  0   7
5   12/26/2019  0   7
5   12/27/2019  0   7

If there is a type of 1 then I only want that data for that data, else if there is only 0 then I want that data for that date.

So for 12/16/2019 I would want the value 0. For 12/23/2019 – 12/27/2019 I would want the value 7.

Answer

You can use row_number() :

select t.*
from (select t.*, row_number() over (partition by id, date order by type desc) as seq
      from table t
     ) t
where seq = 1;

Advertisement

Answer