YogeshChauhan.com
How to Use ROLLUP Operator in SQL and MySQL?
January 4, 2020


When we use grouping or aggregate functions, we can use ROLLUP operator to add one or more summary rows into the result set.

ROLLUP operator is available in SQL and MySQL both with slightly different syntax.

ROLLUP Syntax in SQL:


SELECT 
    column_1, column_2, aggregate_function(column_3)
FROM
    table_name
GROUP BY ROLLUP (column_1,column_2);

ROLLUP Syntax in MySQL:


SELECT 
    column_1, column_2, aggregate_function(column_3)
FROM
    table_name
GROUP BY column_1,column_2 WITH ROLLUP;

Let's look at some examples to understand it better.


//Query in MySQL

SELECT seller_id, 
COUNT(*) AS number_of_invoice,
SUM(invoice_total) AS invoice_total
FROM invoices
GROUP BY seller_id WITH ROLLUP;

//Query in SQL

SELECT seller_id, 
COUNT(*) AS number_of_invoice,
SUM(invoice_total) AS invoice_total
FROM invoices
GROUP BY ROLLUP(seller_id);

In this example, we are using ROLLUP with a single column. In invoices are grouped by seller id in the query. Also, we are calculating number of invoice and sum of invoice total for each seller. 

Checkout the DEMO link given at the end of this article.

Look at the last row. That's a summary row. It's added because of ROLLUP operator in Group By. It summarizes all the aggregate columns in the last row.

So, in the result set the number_of_invoice and invoice_total columns are being summarized by the ROLLUP operator.  

Why there is no value in seller_id column in the summarized row?

That's because it can't be summarized. You'll see null value in SQL and empty rows in MySQL.

Let's take a look at another query.


//Query in MySQL

SELECT seller_state, seller_city, COUNT(*) AS total_sellers FROM sellers
WHERE seller_state IN ('FL','IN')
GROUP BY seller_state, seller_city WITH ROLLUP; 

//Query in SQL
​​​​​​​
SELECT seller_state, seller_city, COUNT(*) AS total_sellers FROM sellers
WHERE seller_state IN ('FL','IN')
GROUP BY ROLLUP(seller_state, seller_city)
ORDER BY seller_name,seller_city; <-- you can use Order by in SQL after ROLLUP

The query above uses 2 columns with ROLLUP operator.

This query groups all the sellers by state and then city. It also counts number of sellers in each group. 

You'll see additional rows in the result set. That's because the summary rows are added for each state and a final summary row as well.

You can also use ORDER BY with ROLLUP in SQL. MySQL doesn't allow to do that.

To deal with null values, you can use the GROUPING function. I'll discuss about it in future article. 

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

How to vertically and horizontally align text and image block (without flex or grid) in CSS?CSSHow to Use Aggregate Functions (MIN, MAX, SUM, AVG, COUNT) to Summarize Data in SQL?SQL/MySQLA Step by Step Guide to Make RSS in XML For Any Website or Blog For FreeMiscOOP, Class and Objects Strategies For Beginners (PHP)PHPHow to Create a Backup From Any Database in SQL?SQL/MySQLWhy does MOV matter in IT or any kind of projects?Misc