SELECT consignor, bol, auction_pallets, lots_sold, FORMAT(Revenue,2) Revenue, FORMAT(GREATEST(0, Total_Cost - Revenue),2) cash_in_play, FORMAT(Total_Cost,2) Total_Cost, FORMAT(Recovered,2) Cost_Of_Sold, ROUND(Total_Cost-Recovered,2) Cost_Unrecovered

FROM

(SELECT

COUNT(li.id) lots_sold,

'All' consignor,

'All' bol,

'-' auction_pallets,

(
SELECT SUM(li2.hammer_price * 1.2 + 7.5) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
WHERE bol2.text=bol3.text AND li2.hammer_price IS NOT NULL
) Revenue,

(
SELECT SUM(cost2.numeric/pallets2.numeric) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
INNER JOIN lot_item_cust_data pallets2 ON (pallets2.lot_item_id = li2.id and pallets2.lot_item_cust_field_id = 74)
INNER JOIN lot_item_cust_data cost2 ON (cost2.lot_item_id = li2.id and cost2.lot_item_cust_field_id = 76)
WHERE bol2.text=bol3.text AND li2.hammer_price IS NOT NULL
) Recovered,

'-' Total_Cost

FROM lot_item li3

LEFT JOIN user u3 ON (u3.id=li3.consignor_id)

INNER JOIN lot_item_cust_data bol3 ON (bol3.lot_item_id = li3.id and bol3.lot_item_cust_field_id = 71)

INNER JOIN lot_item_cust_data pallets3 ON (pallets3.lot_item_id = li3.id and pallets3.lot_item_cust_field_id = 74)

INNER JOIN lot_item_cust_data cost3 ON (cost3.lot_item_id = li3.id and cost3.lot_item_cust_field_id = 76)

WHERE li3.date_sold {DateRange} AND cost3.numeric IS NOT NULL AND u3.username NOT IN ('home depot','Rock and Republic','Acks Liquidation','Clean Bay Area','Qualitycloseouts')

) tmp2