Skip to content

SQL Grouping Sets

What is the Grouping sets statement on sql server? What is the difference with Union? In our queries that contain the Sum function, we combine different grouping schemes using the grouping sets statement. Actually, the process of defragmenting the queries we have grouped with different fields with multiple select methods and getting the aggregate is union. For example: Suppose we want to combine the data summarized for a column and the data summarized for another column. In my example, in my first query, I group and list the amount and amount of sales according to the product. In the second query, I list the sales amount and amount by making a grouping according to the sales staff. I combine these two queries with Union first. I then use the Grouping sets statement instead of Union. Union is used so why use Grouping set? What’s the difference? When we use Grouping sets, we get more performance data than the union we use. It works much faster than Union, especially when we take too many column data and use the where statement. This performance is noticeably noticeable in large databases.


SQL Grouping Sets Examples

CREATE TABLE orders_table
(
	customerID int,
	orderID smallint,
	orderPeriod smallint,
	orderQuantity float
)

INSERT INTO orders_table VALUES (1,2011,1,56)
INSERT INTO orders_table VALUES (1,2010,1,65)
INSERT INTO orders_table VALUES (1,2010,1,59)
INSERT INTO orders_table VALUES (1,2011,1,91)
INSERT INTO orders_table VALUES (1,2011,1,44)
INSERT INTO orders_table VALUES (1,2011,1,79)
INSERT INTO orders_table VALUES (1,2010,1,85)
INSERT INTO orders_table VALUES (1,2010,1,87)
INSERT INTO orders_table VALUES (2,2010,1,54)
INSERT INTO orders_table VALUES (2,2011,1,99)
INSERT INTO orders_table VALUES (2,2010,1,85)
INSERT INTO orders_table VALUES (2,2011,1,47)
INSERT INTO orders_table VALUES (2,2010,1,64)
INSERT INTO orders_table VALUES (2,2011,1,79)
INSERT INTO orders_table VALUES (2,2010,1,74)
INSERT INTO orders_table VALUES (2,2011,1,97)

SELECT * FROM orders_table;
sql Grouping Sets

SELECT ord.customerID, ord.orderID, SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY ord.customerID, ord.orderID
ORDER BY ord.customerID

SELECT ord.customerID, ord.orderPeriod , SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY ord.customerID, ord.orderPeriod
ORDER BY ord.customerID
sql Grouping Sets

SELECT ord.customerID, ord.orderID,NULL, SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY ord.customerID, ord.orderID
UNION ALL
SELECT ord.customerID, NULL , ord.orderPeriod , SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY ord.customerID, ord.orderPeriod
sql Grouping Sets

SELECT ord.customerID, ord.orderID, ord.orderPeriod , SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY GROUPING SETS(
		(customerID,orderID),
		(customerID,orderPeriod)
		)
sql Grouping Sets

SELECT ord.customerID, ord.orderID, ord.orderPeriod , SUM(ord.orderQuantity) as total_order
FROM orders_table as ord
GROUP BY GROUPING SETS(
		(customerID,orderID),
		(customerID,orderPeriod),
		(customerID),
		()
		)
SQL Grouping Sets

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: