ER_BAD_FIELD_ERROR: Unknown column 'distinctAlias.operations_id' in 'field list'

[devfelipereis]

Hello!

I'm trying to make an aggregate query and I'm having this error.

ER_BAD_FIELD_ERROR: Unknown column 'distinctAlias.operations_id' in 'field list'

Of course that operations.id doesn't exists because I'm not selecting it.

Check my query:

const queryBuilder = await Operation.createQueryBuilder('operations');
    queryBuilder.select(
      'DATE_FORMAT(operations.date, "%Y-%m-%d") as date,' +
        'SUM(operations.numberOfContracts) as numberOfContracts, SUM(operations.value) as value,' +
        'wallets.name as walletName, ifnull(SUM(rates.bmf),0) as bmf, ifnull(SUM(rates.brokerage),0) as brokerage,' +
        'ifnull(SUM(rates.emolument),0) as emolument, ifnull(SUM(rates.irrf),0) as irrf, ifnull(SUM(rates.otherCosts),0) as otherCosts',
    );
    queryBuilder.innerJoin('operations.wallet', 'wallet');
    queryBuilder.leftJoin('wallet.rates', 'rates');
    queryBuilder.where('wallet.userId = :userId', { userId: user.id });
    queryBuilder.groupBy('date, operations.walletId');

If I add operations.id as operations_id to the select, then I got this:

QueryFailedError: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.operations.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The last error will desapear if I add operations.id to the group by... but, the returned list will be empty.

I don't know what I'm doing wrong, I'm not a sql expert.

Could you help me? thank you!

[devfelipereis]

I'm doing a messy... I'll try with subqueries.. sorry..