Search code examples
sqlaggregatesql-server-2017

max value in aggregation (sql)

I have some data like so:

diffdat contract    rank
 22         a765      1
  9         b114      1
 33         b114      2
 78          c72      1

I'm trying to get counts aggregating by contract:

  1. max rank = 1 and diffdat < 30

I should get 1 case:

diffdat contract    rank
 22         a765      1

and 2. max rank = 1 and diffdat > 30

diffdat contract    rank
78          c72      1

I have been trying naively using something like

select count(*) from (
select contract from dat group by contract having max(rank) = 1 and diffdat < 30 ) g

But this doesn't seem to work as the data is not aggregated and some cases (b114) are included in both counts (> and < 30)

How can I solve this? Thanks for any help

Solution

  • try by using case when

       select contract, sum(case when rank = 1 and diffdat < 30 then 1 else 0 end),
           sum( case when rank = 1 and diffdat > 30 then 1 else 0 end)
      from dat group by contract