Localmente:

select COALESCE(CONVERT(varchar(50), ca.numero),'TOTAL') AS 'Cartão Nr.',concat(sum(co.valor),' €') as 'Valor Consumido' from cartoes ca right join consumo co on ca.numero+2000=co.mesa where ca.estado=1 group by rollup (ca.numero)

User Reports:

select  ca.numero AS 'Cartão Nr.',concat(sum(co.valor),' €') as 'Valor Consumido' from cartoes ca right join consumo co on ca.numero+2000=co.mesa where ca.estado=1 group by ca.numero with rollup