Postgres SUM help (not like expected)

Question

I would like to SUM balance with the fallowing query

SELECT account_id,partner_id,invoice_id,journal_id,
SUM(account_move_line.balance)
FROM public.account_move_line
WHERE (account_move_line.date BETWEEN '2020-03-01' AND '2020-03-31')
GROUP BY journal_id,account_id,partner_id,invoice_id,account_move_line.id ORDER BY account_move_line.id;

The result expected is the sum of balance for same account_id,invoice_id and journal_id in the result I expected the SUM of -62.63 and -34.43 but in fact not !

capture table

Thanks for your help

Answer 1

Remove the column account_move_line.id from GROUP BY because what you want is to sum the balance for each combination of account_id, partner_id, invoice_id and journal_id:

SELECT 
  account_id,partner_id,invoice_id,journal_id,
  SUM(balance) total_balance
FROM public.account_move_line
WHERE (date BETWEEN '2020-03-01' AND '2020-03-31')
GROUP BY account_id,partner_id,invoice_id,journal_id 
ORDER BY account_id;