SQL

mysql grouping functions

MySQL Grouping Functions
AVG
MAX
BIT_AND
STD
BIT_OR
STDDEV
COUNT
SUM
GROUP_­CONCAT
VARIANCE
MIN

GROUPING function in mysql

/*
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;