Re: SQL Count up MAX records

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

All that does is give you the maximum status, no count.  In my previous 
answer I forgot to name the aggregate field (the result of the MAX).  
You can name it the same as the field you are aggregating, as so (note 
the additional underlined word):

with maxstat as  (
   select gs1, max(status)*_status_ *from table
      where ts between ts1 and ts2
      group by gs1)
select status, count(*) from maxstat
   group by status order by status


On 1/2/2020 2:02 PM, Art Tostaine, Jr. wrote:
> Thanks Joe.  That gave me error Status not found.  This one worked.
>
> with maxstat as  (
>     select count(gs1), max(status) from table
>        where ts between '2019-12-30' and '2020-01-02'
>        group by status)
> select  *    from maxstat