RAW SQL vs. SQL GROUP BY

User Guides
#1

In “standard” SQL you can write a group by like the following

data := [
    ( created_at : "2022-04-28T09:27:20.000Z", count: 2 ),
    ( created_at : "2022-04-28T12:34:20.000Z", count: 3 ),
    ( created_at : "2022-04-27T14:04:20.000Z", count: 4 ),
    ( created_at : "2022-04-26T03:34:20.000Z", count: 1 ),
    ( created_at : "2022-04-26T02:01:20.000Z", count: 2 )
];
 
select count(*), substr(created_at,1,10) 
from data
group by substr(created_at_1,10)

This is not possible in RAW SQL where complexe expressions need to be aliased in order to use them in the SELECT like this:

data := [
    ( created_at : "2022-04-28T09:27:20.000Z", count: 2 ),
    ( created_at : "2022-04-28T12:34:20.000Z", count: 3 ),
    ( created_at : "2022-04-27T14:04:20.000Z", count: 4 ),
    ( created_at : "2022-04-26T03:34:20.000Z", count: 1 ),
    ( created_at : "2022-04-26T02:01:20.000Z", count: 2 )
];
 
select count(*), `date`
from data
group by substr(created_at_1,10) as `date`

This is due to the fact that in RAW SQL we handle nested queries and data.
Note that date is a reserved keyword, hence the surrounding back-ticks.