EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ROLLUP

PostgreSQL ROLLUP

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 4, 2023

PostgreSQL ROLLUP

Introduction to PostgreSQL ROLLUP

The PostgreSQL ROLLUP is an extension of the GROUP BY clause. Generally, the PostgreSQL GROUP BY ROLLUP is used for data analytics operations. For performing data analytics related operations, so many tools or software are available in the market. PostgreSQL is not made for Data Analytics purpose; with the help of the operations like ROLLUP, we can support data analytics operations on real-time data.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

SELECT
c1, c2, c3, aggregate_function(c4)
FROM
table
GROUP BY ROLLUP (c1, c2, c3);

The columns defined in the ROLLUP option get considered for generating hierarchy. Consider the above syntax where we have input columns (c1, c2, c3). The ROLLUP generates all grouping sets considering the hierarchy c1 > c2 > c3. So for reporting sub-total and grand-total, we use the ROLLUP.

In the syntax above, ROLLUP(c1,c2,c3) generates three following grouping sets:

  • (c1, c2, c3)
  • (c1,c2)
  • (c1)
  • ()

We can also perform a partial roll up to reduce the count of sub-totals created.

SELECT
c1, c2, c3, aggregate(c4)
FROM
table_name
GROUP BY
c1, ROLLUP (c2, c3);

How ROLLUP works in PostgreSQL?

  • We can use a single statement to generate multiple grouping sets with the help of the PostgreSQL ROLLUP option.
  • The PostgreSQL ROLLUP option adds extra rows in the result set, allowing us to get total and super-aggregate rows.
  • In order to analyze the hierarchical data like creating grand-total or sub-total, we use the PostgreSQL ROLLUP option.

Examples of ROLLUP in PostgreSQL

Let’s create a table named Furniture.

Code:

CREATE table furniture
(
furniture_id SERIAL PRIMARY KEY,
furniture_name VARCHAR (256) NOT null,
furniture_type VARCHAR (256) NOT null,
furniture_price int NULL
);

Now, insert some data in the furniture table to execute SQL statements.

INSERT INTO furniture (furniture_name,furniture_type,furniture_price)
VALUES
('Chair','Wood',2500),
('Chair','Plastic',2000),
('Table','Wood',5000),
('Table','Plastic',4000),
('Sofa','Wood',10000),
('Sofa','Plastic',8000),
('Bed','Wood',15000),
('Bed','Plastic',13000);

Illustrate the result of the above statement with the help of the following snapshot and the SELECT statement.

SELECT * FROM furniture;

Example #1 – ROLLUP with one column

The following SQL statement uses the GROUP BY clause and the SUM() function to find the total furniture price from furniture_name.

Code:

SELECT
furniture_name, SUM(furniture_price)
FROM
furniture
GROUP BY furniture_name;

Output:

Illustrate the result of the above statement with the help of the following snapshot.

with one column

To fetch the total furniture price of all Furniture, we can use the PostgreSQL ROLLUP to the GROUP BY clause as follows:

SELECT
furniture_name, SUM(furniture_price)
FROM
furniture
GROUP BY ROLLUP (furniture_name);

Output:

Illustrate the result of the above statement with the help of the following snapshot.

postgreSQL ROLLUP 2

You can see the NULL value in the furniture_name column, which shows the grand total super-aggregate result.

In this above example, the PostgreSQL ROLLUP option allows the statement to add an extra row showing the total furniture price.

As we have seen, the output shows a NULL value in a newly produced row, which we can make more readable using the COALESCE() function.

Here we will substitute the NULL value by the ‘Total furniture price’ as follows:

Code:

SELECT
COALESCE(furniture_name, 'Total furniture price') AS furniture_name,
SUM(furniture_price)
FROM
furniture
GROUP BY ROLLUP (furniture_name);

Output:

Illustrate the result of the above statement with the help of the following snapshot.

postgreSQL ROLLUP 3

postgreSQL ROLLUP 4

Example #2 – ROLLUP with multiple columns

The following SQL statement generates the furniture result by furniture_name and furniture_type:

Code:

SELECT
furniture_name, furniture_type, SUM(furniture_price)
FROM
furniture
GROUP BY furniture_name, furniture_type;

Output:

Illustrate the result of the above statement with the help of the following snapshot.

multiple columns

Now add the ROLLUP to the GROUP BY clause as follows:

Code:

SELECT
furniture_name, furniture_type, SUM(furniture_price)
FROM
furniture
GROUP BY ROLLUP (furniture_name , furniture_type);

Output:

Illustrate the result of the above statement with the help of the following snapshot.

postgreSQL ROLLUP 6

Explanation:

  • The set of furniture_type rows for a specified furniture_name, an additional summary row, generates the total furniture price. The values in the furniture_type column are set to NULL in the newly added row.
  • Following all rows, an additional summary row generates the total furniture price of all furniture names and furniture types. The values in the furniture_name and furniture_type columns are set to NULL in the newly added rows.

Example #3 – ROLLUP with a partial rollup

We can use it to do a partial rollup which reduces the count of sub-totals generated, as shown in the following example:

Code:

SELECT
furniture_name, furniture_type, SUM(furniture_price)
FROM
furniture
GROUP BY furniture_name, ROLLUP (furniture_type);

Output:

Illustrate the result of the above statement with the help of the following snapshot.

with a partial

The above example generates an aggregate summary for the furniture_type column, not the furniture_name column.

Conclusion

From the above article, you have seen how to use it. Also, we have added some examples of PostgreSQL ROLLUP to understand it in depth.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL ROLLUP” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL UNION ALL
  2. Learn the PostgreSQL UNIQUE Constraint
  3. PostgreSQL Constraints
  4. PostgreSQL ORDER BY 
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more