Pasted

SELECT bol.text bol, ROUND(pallets.numeric/100) auction_pallets, SUM(IF(s.name="Paid",1,0)) paid, ROUND(pallets.numeric/100) -  SUM(IF(s.name="Paid",1,0)) remaining FROM lot_item li
 LEFT JOIN lot_item_cust_data bol ON (bol.lot_item_id = li.id AND bol.lot_item_cust_field_id = 71)
 LEFT JOIN invoice_item ii ON ii.lot_item_id=li.id
 LEFT JOIN invoice i ON ii.invoice_id=i.id
 LEFT JOIN invoice_status s ON s.id=i.invoice_status_id
 LEFT JOIN lot_item_cust_data pallets ON (pallets.lot_item_id = li.id AND pallets.lot_item_cust_field_id = 74)
 INNER JOIN auction_lot_item ali ON ali.lot_item_id=li.id
 INNER JOIN auction a ON ali.auction_id=a.id
 WHERE bol.text IN (1000035,93004417,2319112,1000036,2319192,6018253,6028511,1000176,1000182,6051849,6043885,6014790,2319163,1000187,1000186,1000181,1000179,1000185,93009718,93009536,1000180,93009619,1000183,2420638,6061212,2319165,2319188,6049496,2420606,6050967,6043536,2319199,2319123,6064928,93009992,93010280,93010146,93009934,93010023,93010303,6086013,93010634,2319008,2420700,6094960,6099116,2420696,1000211,93010339,93010753,1000218,2420666,2420664,2319057,2420669,2420670,5763621,5764889,5769340,5812297,2357291,2357293,2319077)
 AND li.active AND ii.active AND s.name="Paid"
 GROUP BY bol.text;