SELECT id, total, (SELECT SUM(hours * rate) FROM invoice_lines WHERE invoice_id = invoices.id) AS actual_sum FROM invoices WHERE total <> (SELECT SUM(hours * rate) FROM invoice_lines WHERE invoice_id = invoices.id);

To solve the task, you need to group the positions by invoice_id, calculate the sum of hours * rate for each group, and compare it with the total from the invoices table. Then, select those invoices where the sums do not match.

Example SQL query:

SELECT
  i.id,
  i.total,
  COALESCE(SUM(il.hours * il.rate), 0) AS actual_sum
FROM invoices i
LEFT JOIN invoice_lines il ON i.id = il.invoice_id
GROUP BY i.id, i.total
HAVING i.total <> COALESCE(SUM(il.hours * il.rate), 0);

Explanation:

• LEFT JOIN ensures that invoices without positions are also included in the result (if needed).
• SUM(il.hours * il.rate) calculates the sum of the positions.
• COALESCE is used to replace NULL with 0 if there are no positions.
• HAVING filters invoices where the sum of positions does not equal total.

As a result, you will get a table with id, total, and the actual sum of positions actual_sum for invoices with discrepancies.