A Short Query To Get Details And Total Value With “ROLLUP” in MySQL

Searching for a total value of MySQL table fields based on a particular group can use “WITH ROLLUP” feature in MySQL. Usually, a programmer to display the details and the total value of the MySQL data used 2 times query. That is, with the “select * from table where condition …” query to look for details and “select sum (field) from table where condition …” query to find the total value. With “WITH ROLLUP” MySQL Feature we can shorten and ease a query on the database.

For example, we have a sales table as follows:

And we want to display the data as shown below:

We usually do is the following:

With “WITH ROLLUP” feature in MySQL we can display the data along with the total price as shown below:

The above results more similar to the report that had almost finished. I just run the following query to get results:

1 2 3 4

SELECT a.*,SUM(price) AS totalPrice FROM Receipt a GROUP BY receipt_code,autoinc WITH ROLLUP

MySQL has a feature WITH ROLLUP. This feature can put the data per-group. So the system can insert a row its per-group on results. And how easy is to add “WITH ROLLUP” at the end of the GROUP BY syntax.

Actually, using the plain-SQL can make the results as above, but the query is quite long and a little difficult as the following example

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SELECT t.autoinc,t.receipt_code,t.product_name,t.price,t.totalPrice FROM ( SELECT g1.*,g1.price AS totalPrice,COUNT(*) AS rec FROM receipt g1 GROUP BY g1.`receipt_code`,g1.`autoinc` UNION ALL SELECT NULL AS autoinc, MAX(g2.receipt_code) AS receiptCode, g2.product_name, NULL AS price, SUM(g2.price) AS totalPrice, COUNT(*) AS rec FROM receipt g2 GROUP BY g2.receipt_code UNION ALL SELECT NULL AS autoinc, MAX(g3.receipt_code) AS receiptCode, g3.product_name, NULL AS price, SUM(g3.price) AS totalPrice, COUNT(*) AS rec FROM receipt AS g3 ) AS t ORDER BY receipt_code,rec