Get entities grouped by year Trying to write entity queries with group by condition using Drupal::entityQueryAggregate. It works well for simple fields, for example:

$query = \Drupal::entityQueryAggregate('node')-&gt;condition('status', 1)-&gt;condition('type', 'node_type');

$result = $query
      -&gt;groupBy('field_custom_field')
      -&gt;aggregate('nid', 'COUNT')
      -&gt;execute();


But now I have to select nodes aggregated by year from custom field and I can't figure out how to do it:

-&gt;groupBy('YEAR(field_custom_date)') - doesn't work

-&gt;addExpression('YEAR(field_custom_date)', 'fcd_year') - doesn't work

Any ideas how to implement this query?
My final solution, by guidance @CG Monroe:

$query = $this-&gt;connection-&gt;select('node_field_data', 'n');
$query-&gt;innerJoin('node__field_date_created', 'fdc', 'fdc.entity_id = n.nid');
$query-&gt;addExpression('count(n.nid)', 'nid_count');
$query-&gt;addExpression('substring(fdc.field_date_created_value, 1, 4)', 'yr');
$query-&gt;condition('n.type', 'my_type');
$query-&gt;condition('n.status', 1);
$query-&gt;groupBy('yr')-&gt;orderBy('yr', 'ASC');
$result = $query-&gt;execute()-&gt;fetchAll();