Relisted item report

SELECT SUM(total_sale) FROM (

SELECT sales.relisted_item_id, COUNT(*) AS total_sale FROM
    (SELECT ended_items.relisted_item_id, transaction_created_at FROM
    (SELECT relisted_item_id, is_relisted, trial FROM ended_items WHERE is_relisted=1 AND trial<>0 AND store_id=2) ended_items JOIN transactions ON ended_items.relisted_item_id = transactions.item_id) sales
    /*where date(transaction_created_at)='2018-06-17'*/
    GROUP BY sales.relisted_item_id HAVING total_sale > 0

) t1