/*
The GROUPING function is commonly used to replace
the nulls that are generated by WITH ROLLUP with literal values.
The IF function evaluates the expression in the first argument and returns
the second arguments if the expression is true and the third argument if
the expression is false.
#> If you want to display just the summary rows produced by the WITH ROLLUP
operatior, you can include one or more GROUPING functions in the HAVING clause.
*/
SELECT IF(GROUPING(invoice_date) = 1, 'Grand totals', invoice_date) AS invoice_date,
IF(GROUPING(payment_date) = 1, 'Invoice date totals', payment_date) AS payment_date,
SUM(invoice_total) AS invoice_total,
SUM(invoice_total - credit_total - payment_total) AS balance_due
FROM invoices
WHERE invoice_date BETWEEN '2018-07-24' AND '2018-07-31'
GROUP BY invoice_date, payment_date WITH ROLLUP;