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:
Mysql Table Receipt Seegatesite.com

And we want to display the data as shown below:

Receipt Table With Looping Mysql Query

We usually do is the following:

Show Table Data With Php And Mysql Seegatesite

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

Rollup Function Mysql

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

“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

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

The following two tabs change content below.
This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.