How can I append a total to a table and split the table by group (order_id)?
Try this.  I may or may not work, depending on whether you have multiple rows for a given order_id:

select order_id as 'Order ID',
    DATE_FORMAT(date, '%d/%m/%Y') as 'Date',
    prod_title as 'Item',
    SUM(amount) as 'Amount'       -- changed
from transactions
where date &gt;= '2016-10-01'
GROUP BY order_id  WITH ROLLUP    -- added
order by order_id,
    amount desc;


If that fails, then

( SELECT ... as you have, but without ORDER BY ... )
UNION ALL
( SELECT NULL, NULL, 'Total', SUM(amount)
       from transactions 
       where date &gt;= '2016-10-01' )
ORDER BY order_id,
    amount desc;


This will put the Total at the top; that can be fixed by changing to something like

ORDER BY order_id IS NOT NULL, order_id, amount DESC