SQL only supports equivalent grouping. For enumeration grouping, SQL usually uses case when for transition, but cannot keep the order. To keep the order, you have to join. For example, it is written as follows:
with T2(g,ord) as {
       select 'firstGroup',1
       union all select 'secondGroup',2
       ……
}
select T1.*
from T2 join
       (select (case
              when condition1 then 'firstGroup'
              when condition2 then 'secondGroup'
              …… end) g
              , count(*) n
       from A
       group by g) T1
       on T1.g=T2.g
order by T2.ord asc

Moreover, even like this, the loss of empty groups will still occur.

 It would be much more convenient to use SPL in this case. With one line:

=connect(”mysqlDB”).query(“select * from A”).enum([condition1,condition2,…]).new([”firstGroup”,”secondGroup”,…](#):g, ~.len():n)

SPL supports the operation of ordered sets thoroughly. It can express the datasets (including grouped subsets) in the operation process explicitly. In addition to the enumeration grouping of fixed order, it is also easy to realize the overlapping grouping. Please refer to Grouping Data by a Non-field Condition in SPL

When the data is not in the database, it is still convenient for SPL to perform complex calculations:

=file(“d:/t.csv”).import(;,",").enum...

It's also easy to embed esProc into Java applications，please refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc