/* MYSQL - Using ROLLUP */

select vendor, sum(amount), count(amount)
from payment
group by vendor WITH ROLLUP;



/* Rollup by vendor and description */

select vendor, description type, sum(amount), count(amount)
from payment
group by vendor, description WITH ROLLUP;



/* Rollup by vendor and partial description */

select vendor, substring(description,1,4) type, sum(amount), count(amount)
from payment
group by vendor, substring(description,1,4) WITH ROLLUP;



/* Displaying 'total' for all rolled up lines  */
/* Give vendor or =TOTAL= when vendor is null  */

select 
    COALESCE(vendor,      '=TOTAL=')  as vendor,
    COALESCE(description, '-total-')  as description,
    count(amount), sum(amount)
from payment
group by vendor, description WITH ROLLUP;