Validation failed for query for method JPQL

You have tell Spring to treat that query as native one. Otherwise it will try to validate it according to the JPA specification.

Try:

@Query(value = "SELECT ...", nativeQuery = true)
public List<Object[]> transactions();

Keep in mind that cannot use the NEW operator syntax in this case and you would have to deal with result as an array of Object.

Alternatively

If you want to use map the results directly to a POJO class you would have to (assuming that you are using JPA 2.1+):

1) Define the mapping:

@SqlResultSetMapping(
    name="transactionsMapping",
    classes={
        @ConstructorResult(
            targetClass=ConsolidateResDB.class,
            columns={
                @ColumnResult(name="transdate"),
                @ColumnResult(name="orderreqid")
                // further mappings ...
            }
        )
    }
)

2) Define a native query

@NamedNativeQuery(name="transactions"
    , query="SELECT DATE_FORMAT(ts, '%d-%m-%Y') AS transdate, IFNULL(COUNT(orderreqid),0) ... ")

3) Define this method in the CrudRepository without the @Query annotation:

public List<ConsolidateResDB> transactions();

For Spring Data 2.0.5 and above,

After trying solution by Maciej my code started complaining "Got different size of tuples and aliases". To fix the issue in latest release we need to add

@Query(<Entity.FunctionName>, nativeQuery=true). 

This made my code work e2e.