count of nulls over a window

I need to get the count of nulls group by ID but excluding the month and sales in group by

sample data

 id     custname    reportdate   sales
 1        xx        31-JAN-17    1256
 1        xx        31-MAR-17    <null>
 1        xx        30-JUN-17    5678
 1        xx        31-DEC-17    <null>
 1        xx        31-JAN-18    1222
 1        xx        31-MAR-18    <null>
 1        xx        30-JUN-18    5667
 1        xx        31-DEC-18    7890
 2        yy        31-JAN-17    1223
 2        yy        31-APR-17    3435
 2        yy        30-JUN-17    <null>
 2        yy        31-DEC-17    4567
 2        yy        31-JAN-18    5678
 2        yy        31-APR-18    <null>
 2        yy        30-JUN-18    <null>
 2        yy        31-DEC-18    2345

what i need as a output

 id     custname    reportdate   sales  count(Sales nulls)
 1        xx        31-JAN-17    1256     2
 1        xx        31-MAR-17    <null>   2
 1        xx        30-JUN-17    5678     2
 1        xx        31-DEC-17    <null>   2
 1        xx        31-JAN-18    1222     1
 1        xx        31-MAR-18    <null>   1
 1        xx        30-JUN-18    5667     1
 1        xx        31-DEC-18    7890     1
 2        yy        31-JAN-17    1223     1
 2        yy        31-APR-17    3435     1 
 2        yy        30-JUN-17    <null>   1 
 2        yy        31-DEC-17    9643     1
 2        yy        31-JAN-18    5678     2
 2        yy        31-APR-18    <null>   2
 2        yy        30-JUN-18    <null>   2
 2        yy        31-DEC-18    2345     2

As you can see i have multiple years and i need the partition on id and year NOT MONTH

Answer

Using a case expression in count window function.

select t.*,count(case when sales is null then 1 end) over(partition by id) as null_cnt_per_id
from tbl t

Advertisement

Answer