Subquery SUM

[infinitetrinity]

Hi, I'm trying to build this query that retrieve the total payment amount of each event.
Schema is:
Events > Orders > Order Payments

SELECT event_id, count(id) as number_of_orders,
SUM(COALESCE((SELECT SUM(COALESCE(amount, 0)) FROM order_payments WHERE order_id = orders.id),0)) as total_payment
FROM orders
WHERE event_id in (1,2,3)
GROUP BY event_id

So far I got this

final eventIds = events.map((event) => event.id);
final oCount = ordersTable.id.count();
final oPaymentQuery = selectOnly(orderPaymentsTable)
      ..addColumns([orderPaymentsTable.amount.total()])
      ..where(orderPaymentsTable.orderId.equalsExp(ordersTable.id));

final oQuery = selectOnly(ordersTable)
    ..addColumns([
        ordersTable.screeningId,
        oCount,
        subqueryExpression(oPaymentQuery),
    ])
    ..where(ordersTable.eventId.isIn(eventIds))
    ..groupBy([ordersTable.eventId]);

However, this does not return the correct sum of the total payment, only the first payment amount.
Any idea how I can sum it all correctly?

Thank you

[simolus3]

Inside the subquery, the column is SUM(COALESCE(amount, 0)), right? You can write that like

final oPaymentQuery = selectOnly(orderPaymentsTable)
      ..addColumns([coalesce<int>(orderPaymentsTable.amount, Constant(0)).sum()])
      ..where(orderPaymentsTable.orderId.equalsExp(ordersTable.id));

Then, the column you're interested in is SUM(COALESCE(subquery, 0)), which would be

final totalPayment = coalesce<int>(subqueryExpression(oPaymentQuery, Constant(0))).sum();

You should be able to add that column to the topmost query and use it to read the results.