SELECT HOUR(t.transaction_date) hour,AVG(items) avg_items,AVG(t.final_total) avg_value FROM transactions t JOIN (SELECT transaction_id,SUM(quantity) items FROM transaction_sell_lines GROUP BY transaction_id) basket ON t.id=basket.transaction_id WHERE t.type="sell" GROUP BY HOUR(t.transaction_date) ORDER BY hour;