CODEWITHSUNDEEP
sqlsql-servert-sqldategroup-by
TigSh
TigSh

Reputation: 645

Count rows within each group when condition is satisfied Sql Server

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!!

Upvotes: 2

Views: 607

Answers (1)

GMB
GMB

Reputation: 222412

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.

Upvotes: 2

Related Questions