SELECT tb_avoir.client_id, tb_avoir.avoir, tb_avoir_utilise.avoir_utilise, SUM( IFNULL( tb_avoir.avoir, 0 ) - IFNULL( tb_avoir_utilise.avoir_utilise, 0 ) ) AS balance
FROM (

SELECT invoices.client_id, invoices.type, SUM( invoice_lines.quantity * invoice_lines.unit_price * ( 1 + invoice_lines.vat /100 ) ) avoir
FROM `invoice_lines`
LEFT JOIN invoices ON ( invoice_lines.invoice_id = invoices.invoice_id )
WHERE invoices.type =2
GROUP BY `invoices`.`client_id`
) AS tb_avoir
LEFT JOIN (

SELECT invoices.client_id, invoices.type, SUM( -1 * invoice_lines.quantity * invoice_lines.unit_price * ( 1 + invoice_lines.vat /100 ) ) avoir_utilise
FROM `invoice_lines`
LEFT JOIN invoices ON ( invoice_lines.invoice_id = invoices.invoice_id )
WHERE invoices.type !=2
AND invoice_lines.unit_price & lt;

0 GROUP BY `invoices`.`client_id`
) AS tb_avoir_utilise ON tb_avoir_utilise.client_id = tb_avoir.client_id GROUP BY tb_avoir.client_id