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
0
votes
1 answer

"Each GROUP BY expression must contain at least one column that is not an outer reference" with "grouping set"

I have the following SQL query, my goal was to make a subtotal and a general tota, grouping the subtotals by products, but my problem would be that if I use only the "group by" he would have to add all the fields of the "select", and the result…
Rafael Perbeline
  • 7
  • 4
0
votes
1 answer

Oracle hierarchial queries

I am trying to pull out all the hierarchy values for a particular segment in Oracle Apps(referring tables applsys.fnd_flex_Value_norm_hierarchy & applsys.fnd_flex_values). I want a tree like display starting from the top parent and going down until…
aleesha rajwanshi
  • 1
0
votes
1 answer

Can we do rollup or cube without exploding rows in hive/spark

I'm aggregating 4 dimensions on a hive table (tens of billions of rows), and they need to be rolled up or cubed. Say my table is about user interactions, and I will aggregate on what mobile application they use, what mobile os they have, etc. The…
Xintong Bian
  • 45
  • 1
  • 6
0
votes
2 answers

How to rename the column with grouping sets in Oracle

I calculate the total amount using grouping sets SELECT CASE WHEN GROUPING(Name) = 1 THEN 'TOTAL' ELSE Name END, ID, SUM(Amount) FROM Table1 GROUP BY GROUPING SETS ( (ID, Name), (ID) ); I will get something like this ID Name …
HenlenLee
  • 435
  • 1
  • 11
  • 30
0
votes
2 answers

How to create two Grand Total rows using SQL - Totals and Averages

I am needing to create two rows that contain Totals as opposed to the typical one totals row. Grand Totals and Averages. I am creating a report using basic SQL and I am working out of an Oracle database but I am not using any PL/SQL. I'm…
Code Novice
  • 2,043
  • 1
  • 20
  • 44
0
votes
2 answers

T-SQL - using GROUP BY GROUPING SET - Filter single set

in this statement from #base U group by grouping sets ( (a,b,c,d) --1 ,(a,b,c,d,e,f) --2 ,(a,b,c,d,e,f,g) --3 ) is possibile in a single set an filter before aggregation ? (a,b,c,d,e,f) where b <> 0
pakos asd
  • 1
0
votes
1 answer

Grouping sets is not aggregating results

I've only recently started using this function, so forgive this question if it is imbecilic. I have an existing query, which I've realised could be helped with the use of this query to create additional group by groupings, akin to subtotals. In…
Asher
  • 348
  • 1
  • 3
  • 19
0
votes
1 answer

SQL grouping sets and roundup

I am trying to calculate the difference between a certain sum of values and the same sum using specific roundup rules (columns 5 and 6): select A, B, C, sum(D), sum(D)/300, case when sum(D)/300 < 1.5 then 0 else…
Victor
  • 1
  • 1
0
votes
1 answer

Syntax of using case in grouping sets

The Hive query snippet in question is as below: group by case when inte.subId is not null then 'int' else 'ext' end, taskType, result grouping sets( ( case when inte.subId is not null then 'int' else 'ext' end, …
Lingxi
  • 14,579
  • 2
  • 37
  • 93
0
votes
0 answers

How to add subtotal row after the bottom row using a grouping set in SQL Server 2014

I am having a problem trying to get the SUBTOTAL row to be at the BOTTOM of the rows I'm subtotaling. Instead the SUBTOTAL row is at the TOP of the rows I'm subtotaling. I'm working with SQL Server 2014. If someone could please provide any…
Melinda
  • 1,501
  • 5
  • 25
  • 58
0
votes
0 answers

SQL GROUPING SETS averages with multiple many-to-many dimensions

I have a table of data with the…
rinkutown
  • 1
0
votes
1 answer

Getting grand totals with grouping sets

I am migrating SQL SERVER 2005 to SQL SERVER 2014 and came across this problem. I have a "search engine" STORED PROCEDURE that builds report queries on the fly for my reporting website. In the old version I used COMPUTE BY to get sub totals and…
Val K
  • 301
  • 3
  • 9
  • 22
0
votes
1 answer

SQL Stored procedure - Grouping Sets with a where clause possible?

I have used Grouping Sets before to sum up everything in a column to get a grand total. But, I have not found a way to sum up the columns with a WHERE CLAUSE included and I was wondering if there is actually a way to do this using Grouping…
Justin
  • 954
  • 4
  • 22
  • 44
0
votes
1 answer

Multiple groupings without SPD

our client wants multiple groupings in a Sharepoint 2010 list. Aprox 6 grouping headers. Unfortunatelly we are not allowed to use SPD. is there any way that i can create multiple groupings without SPD?
psparla
  • 1
0
votes
1 answer

Incorrect syntax near 'SETS'. - SQL Server 2005

My code: Group BY GROUPING SETS ( (wmsSponsorEntityName), (wmsSponsorEntityPrimaryRegion), (wmsProjectNameSWP) , () ) I'm doing this because those three fields repeat and the other…
Meagan K Warncke
  • 13
  • 4
1 2
3