How to retrieve and group multiple columns from a query in Esper EPL?

Question

I am writing a query to get the first character of the title field of the inputStream and also counting the types of characters returned.

Individually these two queries work and gives me the count of results and the characters after sub-stringing.

"select count(*) as count as character from Data.win:time_batch(5 sec) ";

"select title.substring(0,1) as character from Data.win:time_batch(5 sec) ";

But when I combine both of them and group by character I get an error with the grouping.

public String getStatement() {

    return "select count(*) as count, title.substring(0,1) as character from 
                                    Data.win:time_batch(5 sec) group by character";
}

/**
 * Listener method called when Esper has detected a pattern match.
 */
public void update(Map<String, String> eventMap) {
    System.out.println(eventMap);
    // Titles in the last 5 seconds
    String character = (String) eventMap.get("character");
    String count = (String) eventMap.get("count");
    StringBuilder sb = new StringBuilder();
    sb.append("---------------------------------");
    sb.append("\n- [MONITOR] Char = " + character + " Count =" + count);
    sb.append("\n---------------------------------");

    LOG.debug(sb.toString());
}

Can anyone suggest on how to go about grouping and returning both the fields in select statement.

Answer 1

The select-clause in EPL does not name expressions globally.

For sharing named expressions within EPL or globally Esper has expressions (see 5.2.9) for examples.

An alternative is:

insert into Character select title.substring(0,1) as character from Data

select count(*) as count, character from Character.win:time_batch(5 sec) group by character