Stack Overflow
Stack Overflow

Questions tagged [grouping-sets]

The GROUPING SETS operator is an extensions of the GROUP BY clause. It can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using GROUPING SETS operator is usually more efficient.

The GROUPING SETS operator is an extensions of the GROUP BY clause. It can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using GROUPING SETS operator is usually more efficient.

The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want.

The GROUPING SETS list can contain duplicate groupings; and, when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL.

More examples can be found here.

45 questions
1
vote
1 answer

Optimizing query with multiple sums?

I have table products: +----------+-----------+----------+---------+ |family_id |shopper_id |product_id|quantity | +----------+-----------+----------+---------+ |A |1 |Kit Kat |10 | |A |1 |Kit Kat |5 …
alhazen
  • 1,907
  • 3
  • 22
  • 43
1
vote
1 answer

A column in a Grouping Set is NULL when it shouldn't be = SQL Server bug, or more likely, do I not understand Grouping Sets?

I'm running the script below on SQL Server 2019 Developer edition. You can run this on your own SQL Server, any database is fine (no specific schema required). At the bottom of my script I have a query with 2 grouping sets in the GROUP BY clause. …
schaneville
  • 23
  • 6
1
vote
0 answers

SQLs with Grouping Set do not return the same result

I have 2 queries with Grouping set. Before doing grouping set, I have checked the result are totally the same. The values of L_TIM_53_ID and L_TIM_54_ID are always 2458120. But I got a different result with the following 2 Queries. Query A, SELECT…
Knight Shao
  • 89
  • 2
  • 8
1
vote
0 answers

32 column limit for grouping sets

I am still working on sql 2005 to sql 2014 server migration. With the older server, using COMPUTE BY, I was able to create a report that had 57 columns with some of those column being aggregate fields. There was no limit to the amount of columns I…
Val K
  • 301
  • 3
  • 9
  • 22
0
votes
2 answers

Is it possible to use grouping sets to achieve this type of aggregation using multiple joins

I am able to get the correct results using UNION ALL - but have been trying to get this to work using GROUPING SETS without success. Is this possible? Example SQL: --Build data table WITH TABLE_1 AS ( --data SELECT 1 T1_ID, 2 VAL UNION SELECT 2…
zapf2023
  • 19
  • 4
0
votes
1 answer

Grouping_sets getting different output formats when group by changed

I have the following test CASE which is working fine. When I try to group by customer_id, TO_CHAR (p.purchase_date, 'IYYY"W"IW') I'm not getting the same format as the working query. As a workaround I could limit the query to 1 customer_id each time…
Beefstu
  • 804
  • 6
  • 11
0
votes
0 answers

Postgres grouping sets - Show count zero or named groups

I have the following query: select count(*) from "my_table" where field1 = true and field2 = 'delivered' group by grouping sets((field3), (field4), (field5)) having field3 = true or field4 = true or field5 For each group in the grouping set, I want…
markvdlaan93
  • 613
  • 10
  • 26
0
votes
1 answer

Why when using grouping_id with rollup the total will be level 3 instead of 2?

Is there any logic behind the reason to why the total grouping when using rollup will be "lvl" 3?.. example: col1 col2 id1 1 id1 2 id2 1 id2 2 id3 1 id3 2 In cube method this is understandable as level 0 is the "basic values"…
Gal Mor
  • 1
  • 2
0
votes
1 answer

Postgresql - Apply a group by across multiple columns on a very large table

I'm working with a very large table (~425 million rows, ~800 columns, ~750GB on disk) that looks like (4 columns included here): id | c1 | c2 | c3 | c4 -----+----+--------+--------+---- 101 | t | green | small | 10 102 | t | green |…
ricardomart
  • 9
  • 1
0
votes
2 answers

How to make SQL query with grouping in each node of an hierarchy tree?

There is a table with an usual "child-to-parent" structure, where each node has weight. TREE_TABLE ---------------------------------------- id | parent_id | name | weight ---------------------------------------- 1 | NULL | N1 …
diziaq
  • 6,881
  • 16
  • 54
  • 96
0
votes
1 answer

How to distinguish between real NULL and formal NULL in Postgres ROLLUP?

I am trying to determine what "level" of a ROLLUP report each given row belongs to. When initial data contains no NULLs, then it is possible just count nulls in every row (amount of null cells is the same for each given level/layer of grouping),…
diziaq
  • 6,881
  • 16
  • 54
  • 96
0
votes
0 answers

Using MonetDB grouping sets with Pentaho Mondrian

I know MonetDB supports grouping sets. I also know that Mondrian supports them too, but only in a couple of databases. Is it possible to enable SQL groupings sets in Mondrian for MonetDB database? Thank you!
Llorieb
  • 25
  • 5
0
votes
1 answer

Calculating multiple averages across different parts of the table?

I have the following transactions table: customer_id purchase_date product category department quantity store_id 1 2020-10-01 Kit Kat Candy Food 2 store_A 1 …
alhazen
  • 1,907
  • 3
  • 22
  • 43
0
votes
1 answer

oracle sum by column without using union

I have this table: (supply table: how many products in storages) Storage_id product_id amount 1 1000 55 1 1005 1 ... 29 1000 3 29 …
user9741285
0
votes
1 answer

How to overcome grouping() function not supported by HANA Table UDF?

I have written some custom logic in HANA anonymous block. I used grouping function somewhere in the middle as a core. It worked well and the result was satisfying. Once I tried to industrialize it by moving to HANA Table Function, the activation…
wounky
  • 97
  • 1
  • 12
1
2
3