SQL GROUP BY WITH ROLLUP

The GROUP BY statement is used in SQL to group multiple results and use functions on the grouping. WITH ROLLUP command allows you to add an extra line that works as a “super-aggregator” system on all the results.
 

Syntax of GROUP BY … WITH ROLLUP

In general, the GROUP BY … WITH ROLLUP command is used as follows:

SELECT column1, function(column2)
FROM table
GROUP BY column1 WITH ROLLUP

 

 

Example :

Let’s consider a “customer” table that lists the customers, their zip code, their orders, their registration date:

Now, it is possible to assume that we want to get information for each zip code, such as :

• The number of customers for each zip code
• Number of orders for each zip code
• The date of first registration by zip code
• The date of last registration by zip code

The above requirement will be easily done thanks to GROUP BY. The addition of the “WITH ROLLUP” command will allow to obtain a line with the following additional information (whatever the zip code):

• Total number of customers
• The number of total orders
• Date of first registration
• Date of last registration

In order to get the information related to this need, the SQL query will be the following:

SELECT `zip_code`, COUNT(*), SUM(`order`), MIN(`registration_date`), MAX(`registration_date`)
FROM `customer`
GROUP BY zip_code WITH ROLLUP

 

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More