Q:

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:

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

A:

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

