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:

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

SELECT a.*,SUM(price) AS totalPrice

FROM Receipt a

GROUP BY receipt_code,autoinc

WITH ROLLUP

Table of Contents

1 “WITH ROLLUP” MySQL Feature
- 1.1 Thus short tutorial on “WITH ROLLUP” MySQL Feature to get for detail and the total value of a field, may be useful 🙂

“WITH ROLLUP” MySQL Feature

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

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