• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
seegatesite header

Seegatesite.com

Seegatesite.com - Programming Tutorial , Sharing , How and Learn Together

  • TOOLS
    • Bootstrap Navbar Online Generator
    • Customize Sidebar Menu Bootstrap 3
    • Bootstrap Demo
  • ADVERTISE
  • CATEGORIES
    • Android
    • Blogging Tips
    • Database
    • CSS
    • Info Gadget
    • Javascript
    • Linux
    • PHP
    • Various
    • WordPress
  • Q&A
  • PHP
  • JAVASCRIPT
  • JQUERY
  • ANGULAR
  • WORDPRESS
  • SEO
  • REACT
🏠 » Database » A Short Query To Get Details And Total Value With “ROLLUP” in MySQL

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

By Sigit Prasetya Nugroho ∙ March 29, 2017 ∙ Database ∙ Leave a Comment

Share : TwitterFacebookTelegramWhatsapp

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

Related Articles :

  • Easy Way Convert SQL Query to Laravel Builder Using Orator
  • Using “str_replace PHP” In MySQL With REPLACE Function
  • How To Back Up The Entire MySql Database with PHP

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:

1
2
3
4
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

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

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

Another Database Related Post :

  • Firebase CRUD – JQuery Tutorial For Beginners
  • Using “str_replace PHP” In MySQL With REPLACE Function

Avatar for Sigit Prasetya Nugroho

About Sigit Prasetya Nugroho

This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Welcome to my Home,

Avatar for Sigit Prasetya NugrohoThis site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.



Popular Articles

Checked checkbox AdminLTE Bootstrap in Jquery

November 4, 2014 By Sigit Prasetya Nugroho 7 Comments

Simple create date format validation with jqueryUI

December 21, 2014 By Sigit Prasetya Nugroho Leave a Comment

Create Simple Progress Bar for Fake Online Generator with Jquery

January 10, 2015 By Sigit Prasetya Nugroho Leave a Comment

22+ Coolest Free Jquery Plugin For Premium Theme

October 3, 2015 By Sigit Prasetya Nugroho Leave a Comment

Easy Build Your Anti Copy Paste Plugin

October 6, 2015 By Sigit Prasetya Nugroho Leave a Comment

Popular Tags

adminlte (15) adsense (13) adsense tips (4) affiliate amazon (13) amazon (12) Android (8) angular (16) angular 4 (12) angular 5 (4) asin grabber (3) Bootstrap (27) codeigniter (5) create wordpress theme (5) crud (8) css (6) free wordpress theme (7) google adsense (4) imacros (4) increase traffic (6) jquery (34) laravel (10) laravel 5 (5) learn android (5) modal dialog (5) mysql (6) nodeJs (4) optimize seo (4) pdo (6) php (30) plugin (53) pos (7) Publisher Tips (5) react (3) Reactjs (7) SEO (37) theme (17) tutorial angular (5) tutorial angular 4 (6) tutorial javascript (10) tutorial javascript beginners (4) twitter (3) widget (3) wordpress (18) wordpress plugin (13) XMLRPC (5)




  • About
  • Contact Us
  • Disclaimer
  • Privacy Policy
  • Terms and Conditions

©2021 Seegatesite.com