---
title: "Popular ROLLUP SQL query to apply on your data to group it quickly"
description: "ROLLUP and grouping sets are two similar functions to apply on group of data. Most popular SQL query example to use ROLLUP. You can improve your SQL performance."
url: https://srinimf.com/2015/06/02/rollup-or-sub-total-in-db2-v11-sql/
date: 2015-06-02
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2018/05/pexels-photo-1108764-e1527419140450.jpeg?fit=500%2C331&ssl=1
categories: ["DB2 V11"]
tags: ["DB2 Tips and Solutions", "ROLLUP SQL query in DB2"]
type: post
lang: en
---

# Popular ROLLUP SQL query to apply on your data to group it quickly

A ROLLUP grouping is an extension to the GROUP BY clause that produces a result set containing sub-total rows in addition to the “regular” grouped rows.

[![ROLLUP](https://i0.wp.com/srinimf.com/wp-content/uploads/2015/06/rollup.jpg?resize=680%2C485&ssl=1)](https://i0.wp.com/srinimf.com/wp-content/uploads/2015/06/rollup.jpg?ssl=1)

Subtotal rows are “super-aggregate” rows that contain further aggregates whose values are derived by applying the same column functions that were used to obtain the grouped rows. These rows are called sub-total rows, because that is their most common use; however, any column function can be used for the aggregation.

Related[PL1 mostly used Logical Expressions](https://srinimf.com/2010/12/20/pl1-logical-expressions/)

A ROLLUP grouping is a series of grouping-sets. The general specification of a ROLLUP with “n” elements.

**Syntax:**

GROUP BY ROLLUP(C1,C2,…,Cn-1,Cn)

It is equivalent to [Grouping sets](https://srinimf.com/2015/05/27/reduce-sql-complexity-with-grouping-sets-in-db2-v11/):

GROUP BY GROUPING SETS((C1,C2,…,Cn-1,Cn),

(C1,C2,…,Cn-1),

…

(C1,C2),

(C1),

() )

**SQL for ROLLUP**

```
SELECT workdept, 
       edlevel, 
       sex, 
       Sum(salary) AS SUM_SALARY, 
       Avg(salary) AS AVG_SALARY, 
       Count(*)    AS COUNT 
FROM   dsn81110.emp 
WHERE  salary > 20000 
GROUP  BY rollup ( workdept, edlevel, sex )
```

**Result:**

```
WORKDEPT EDLEVEL SEX SUM_SALARY AVG_SALARY COUNT

A00     14      M    29250.00 29250.00000000 1
A00     18      F    52750.00 52750.00000000 1
A00     19      M    46 500.00 46500.00000000 1
B01     18      M    41250.00 41250.00000000 1
C01     16      F    23800.00 23800.00000000 1
.
.
.
NULL   NULL NULL 750075.00 30003.00000000 1
```

Note that the ‘n’ elements of the ROLLUP translate to n+1 grouping sets. Note also that the order in which the grouping-expressions is specified is significant for ROLLUP.

**ROLLUP and Grouping sets SQL:**

GROUP BY ROLLUP(a,b)

Is equivalent to

GROUP BY GROUPING SETS((a,b), (a), () )

While GROUP BY ROLLUP(b,a)

Is the same as

GROUP BY GROUPING SETS((b,a), (b), () )

With support for ROLLUP, cube, and grouping-sets specifications, the SQL coding complexity can be reduced greatly and the SQL performance can be improved dramatically.

Related[9 Must-Read UNIX Blogs to Sharpen Your Interview Skills](https://srinimf.com/2014/05/13/unix-commands-mainframe-informatica/)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F06%2F02%2Frollup-or-sub-total-in-db2-v11-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2018%2F05%2Fpexels-photo-1108764-e1527419140450.jpeg%3Ffit%3D500%252C331%26ssl%3D1&description=Popular%20ROLLUP%20SQL%20query%20to%20apply%20on%20your%20data%20to%20group%20it%20quickly)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/06/02/rollup-or-sub-total-in-db2-v11-sql/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/06/02/rollup-or-sub-total-in-db2-v11-sql/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/06/02/rollup-or-sub-total-in-db2-v11-sql/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/06/02/rollup-or-sub-total-in-db2-v11-sql/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Popular%20ROLLUP%20SQL%20query%20to%20apply%20on%20your%20data%20to%20group%20it%20quickly&body=https%3A%2F%2Fsrinimf.com%2F2015%2F06%2F02%2Frollup-or-sub-total-in-db2-v11-sql%2F&share=email)
-

### Like this:

Like Loading…