When enumerating multiple conditions for grouping, there may be overlaps. The overlapped part is only included in one group or all the eligible groups. When writing SQL, the writing method is quite different.

When the overlapped part is only included in the first eligible group, it can be converted to a regular grouping through case when, for example:

select (case
              when age<=18 then ‘Teenager’
              when age>=16 and age<=30 then ‘Youth’
              when age>=28 and age<=45 then ‘MiddleAge’ end) g
       ,count(*) n
from A
group by g

If the overlapped part is to be included into all groups, it will be much more troublesome. SQL only supports equivalent grouping, and cannot implement external definition grouping and alignment grouping. Generally, we can only traverse according to each condition, and then union the result set. When there are many conditions, the scale and performance of SQL statements are worrying:

(select ‘Teenager’ g, count(*) n
from A
where age<=18)
union
…Youth…
union
…MiddleAge…

This kind of unconventional grouping would be much easier if you use esProc SPL. For the above problem, pass grouping conditions and names through parameters

>w=[?<=18,?>=16&&?<=30,?>=28&&?<=45]
>wn=["Teenager","Youth","MiddleAge"]

Only one line can realize non overlapping / overlapping (increase @r option) grouping:

=connect(”mysqlDB”).query(“select * from A”).enum@r(w, age).new(wn(#):g, ~.len():n)

This SPL sentence also ensures the order of the returned result set, and that records that do not match will not be missed.

SPL can also realize more unconventional 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