MySql Function

MySql Rollup function :


The GROUP BY clause permits a WITH ROLLUP modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) operations.

Suppose that a sales table has year, country, product, and profit columns for recording sales profitability:

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

To summarize table contents per year, use a simple GROUP BY like this:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+
Now Same Example with rollup function 
mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+
here you can see null which is actually representing summation of both year.

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC, country ASC, product ASC WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+


Other Considerations When using ROLLUP

The following discussion lists some behaviors specific to the MySQL implementation of ROLLUP.

1: When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results.

2: Use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns. In this case, the super-aggregate summary rows added by ROLLUP still appear after the rows from which they are calculated, regardless of the sort order.




3:To work around the restriction that prevents using ROLLUP with ORDER BY, generate the grouped result set as a derived table and apply ORDER BY to it. For example:




SELECT * FROM
(SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP) AS dt
ORDER BY year;

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year ASC WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+
This behavior is permitted when the ONLY_FULL_GROUP_BY SQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal because country is not listed in the GROUP BY clause. With ONLY_FULL_GROUP_BY enabled, you can still execute the query by using the ANY_VALUE() function for indeterminate-value columns: