EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL GROUPING SETS

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL GROUPING SETS

SQL GROUPING SETS

Introduction to SQL GROUPING SETS

GROUPING SET in standard query language (SQL) can be considered as a sub-clause of GROUP BY clause. For uninitiated, GROUP BY clause is used to group rows having the same values in a column into summary rows. A grouping set is a set or group of columns by which rows with similar values are grouped together. Functionally, it generates a result set similar to the one generated by a UNION ALL of multiple GROUP BY clauses on a single column. Some other sub-clause of GROUP BY clause such as ROLLUP, CUBE etc also produce result sets equivalent to GROUPING SETS.

Syntax and parameters:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The basic syntax for working with GROUPING SETS() in SQL is as follows :

SELECT
column1,
column2,
aggregate_function(column3)
FROM
table_name
GROUP BY
GROUPING SETS (
(column1, column2),
(column1),
(column2),
()
);

The parameters used in the above mentioned syntax are as follows:

  • column1, column2: The columns or field names that have to be fetched for the final result set.
  • aggregrate_function(column3): The aggregate function and column name on the basis of which group summary will be prepared.
  • GROUPING SETS: Set of columns that have to be grouped together.

Examples of SQL GROUPING SETS

In order to illustrate grouping sets in SQL, let us create a dummy to table called “products”. It contains product details such as product_id, price, brand and manufacturing date.

Code:

CREATE TABLE products(
product_id character varying(255),
category character varying(255),
price  numeric,
brand character varying(255),
manufacturing_date date
);

Output:

SQL GROUPING SETS 1

We have successfully created the table. Next, let us insert some values in the table using the following INSERT query.

Code:

INSERT INTO public.products(
product_id, category, price, brand, manufacturing_date)
VALUES ('G1','Women Jeans',3500,'Levis','2020-01-01'),
('G2','Women Jeans',2300,'M&S','2019-11-12'),
('G3','Women Jeans',5000,'Levis','2019-12-01'),
('G4','Women Top',2500,'Levis','2020-11-01'),
('G5','Women Top',1600,'M&S','2020-11-01'),
('G6','Women Top',3200,'M&S','2020-01-01'),
('G7','Women Jeans',1500,'Levis','2020-12-01'),
('G8','Women Top',3500,'Next','2020-01-01'),
('G9','Women Jeans',2100,'Next','2020-11-01'),
('G10','Women Top',1654,'Next','2020-12-01');

Output:

SQL GROUPING SETS 2

The products table after successful insertion operation looks something like this:

Code:

SELECT * FROM products;

Output:

SQL GROUPING SETS 3

Example #1

Grouping sets in SQL is considered equivalent to UNION ALL on multiple group by clauses. So, our first example is an illustration of the same.

Consider the following SQL queries with GROUP BY clause on category, brand, manufacturing month and finally a summary.

Popular Course in this category
JDBC Training (6 Courses, 7+ Projects)6 Online Courses | 7 Hands-on Projects | 37+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (6,044 ratings)
Course Price

View Course

Related Courses
PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)SQL Training Program (7 Courses, 8+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

a. SQL query to find total amount for each category.

Code:

SELECT category, sum(price) as total_amount
FROM products
GROUP BY category;

Output:

total amount for each category

b. SQL query to find total amount for each brand.

Code:

SELECT brand, sum(price) as total_amount
FROM products
GROUP BY brand;

Output:

SQL GROUPING SETS 5

c. SQL query to find total amount for each month of manufacturing.

Code:

SELECT EXTRACT(month FROM manufacturing_date),
sum(price) as total_amount
FROM products
GROUP BY EXTRACT(month FROM manufacturing_date);

Output:

total amount for each month of manufacturing

d. SQL query to find total amount for overall.

Code:

SELECT NULL,SUM (price) as "summary"
FROM
products;

Output:

total amount for overall

When we combine all of the above GROUP BY queries using UNION ALL as shown below, we get a result set equivalent to the result set obtained by GROUPING SETS.

Code:

SELECT category, sum(price) as total_amount
FROM products
GROUP BY category
UNION ALL
SELECT brand, sum(price) as total_amount
FROM products
GROUP BY brand
UNION ALL
SELECT EXTRACT(month FROM manufacturing_date) :: varchar,
sum(price) as total_amount
FROM products
GROUP BY EXTRACT(month FROM manufacturing_date)
UNION ALL
SELECT NULL,SUM (price) as "summary"
FROM products;

Output:

SQL GROUPING SETS 8

Now observe this next query. Here, we have used grouping sets to group category, brand and month of manufacturing together.

Code:

SELECT category, brand,
EXTRACT(month FROM manufacturing_date),
sum(price) as "total_amount"
FROM products
GROUP BY
GROUPING SETS (category,
brand,
EXTRACT(month FROM manufacturing_date),
());

Output:

SQL GROUPING SETS 9

What do you observe? We observe that the result set obtained from both the queries is the same. The only difference is of NULL values, we can coalesce them using COALESCE function. But most importantly, the second query is more concise and easier to understand.

Example #2

Prepare a summary table for each category brand wise with the total amount under each group.

Code:

SELECT category, brand,
sum(price) as "total_amount"
FROM products
GROUP BY
GROUPING SETS ((category, brand),
())
ORDER BY brand,category;

Output:

for each category brand wise with the total amount

In this example, we have grouped category and brand together as a single set. Hence, within each category we see further brandwise grouping.

Example #3

Prepare a summary table with count and total amount of brand wise yearly manufacturing of products.

Code:

SELECT brand, extract(year FROM manufacturing_date),
count(product_id) as "total_units",
sum(price) as "total_amount"
FROM products
GROUP BY
GROUPING SETS ((brand, 2),
())
ORDER BY brand;

Output:

with count and total amount of brand

Here, we have found the total units and the total amount of garment manufactured for each brand on a yearly basis by grouping brand and year of manufacturing together in a single set.

Conclusion

Grouping sets is like sub-clause under GROUP BY clause that help in preparing summary tables along multiple dimensions. It is equivalent to performing UNION ALL on more than one GROUP BY queries. But it’s more concise and the query is easier to understand.

Recommended Articles

This is a guide to SQL GROUPING SETS. Here we discuss the introduction to SQL GROUPING SETS along with examples respectively. You may also have a look at the following articles to learn more –

  1. SQL DATEPART()
  2. SQL Users
  3. SQL DECODE()
  4. Column in SQL

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • 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

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

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

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

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

Special Offer - JDBC Training Course Learn More