Count rows within each group when condition is satisfied Sql Server

user_9592484 0
Viewed 226

I have at table which looks like below:

         ID            Date     IsFull
         1          2020-01-05    0 
         1          2020-02-05    0  
         1          2020-02-25    1  
         1          2020-03-01    1
         1          2020-03-20    1

I want to display how many months for ID = 1

have sum(isfull)/count(*) > .6 in a given month (More than 60% of the times in that month isfull = 1)

So the final output should

 ID     HowManyMonths
  1       1     --------(Only month 3----2 out 2 cases)

If the question changes to sum(isfull)/count(*) > .4

then the final output should be

  ID     HowManyMonths
   1       2     --------(Month 2 and Month 3)

Thanks!!

group-by date tsql sql-server sql
1 Answers

You can do this with two levels of aggregation:

select id, count(*) howManyMonths
from (
    select id
    from mytable
    group by id, year(date), month(date)
    having avg(1.0 * isFull) > 0.6
) t
group by id

The subquery aggregates by id, year and month, and uses a having clause to filter on groups that meet the success rate (avg() comes handy for this). The outer query counts how many month passed the target rate for each id.

user_106767160
Related