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
18
votes
6 answers

Overall summary with multiple GROUP BY

Lets say I have a table called census with the following information: COUNTRY PROVINCE CITY POPULATION ============================================== USA California Sacramento 1234 USA California SanFran 4321 USA…
Mr. Llama
  • 20,202
  • 2
  • 62
  • 115
5
votes
1 answer

Grouping sets by year, month, and date

Is it possible to rollup the date, month, and year using GROUPING SETS, where the dates are shownn and grouped into month, and year, and the value for month and year are displayed instead of being null? Here is the sample data, and my attempt at…
To Do
  • 151
  • 11
4
votes
2 answers

postgresql, named grouping sets?

Is there a way to name grouping sets? For each grouping set (explicitly defined or generated with rollup or cube as per https://www.postgresql.org/docs/devel/static/queries-table-expressions.html), I'd like to somehow specify a name in a result…
Sigfried
  • 2,943
  • 3
  • 31
  • 43
3
votes
1 answer

How to remove null values from a postgres grouping sets query

I wanted to do multiple independent group-bys in a single query, so that I could get the group wise count of different columns in a single query. I achieved that using the following sample query: SELECT model, count(model),…
Akshar Vibhav
  • 53
  • 5
2
votes
2 answers

Query using CTE and UNION ALL with Grand Total at bottom

This is the simplified version or representative of the SQL Server query that I am attempting: WITH T1 AS ( SELECT DocNum, CardCode, CardName FROM OINV ) SELECT CardName AS 'Customer Name', DocNum FROM T1 UNION ALL SELECT 'Grand Total',…
AllSolutions
  • 1,176
  • 5
  • 19
  • 40
2
votes
2 answers

"Adding" GROUP BY's and ORDER BY's in one query instead of separate

I have a base query that has a few variations, all of them are different from each other in the GROUP BY and ORDER BY clauses. These are the variations: SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c" FROM my_table GROUP…
pileup
  • 1
  • 2
  • 18
  • 45
2
votes
2 answers

Oracle - Grouping Sets and Pipelined Table Functions (expected NUMBER got ROW)

I'm working on a report with summary logic using GROUPING SETS, but I'm getting this error: SELECT c1, c2, c3, SUM(c4) AS MySum FROM TABLE(get_data()) src GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2,…
ravioli
  • 3,749
  • 3
  • 14
  • 28
2
votes
1 answer

How to reshape data after GROUPING SETS in Hive?

I would like to aggregate a column over many different dimensions. I think GOUPING SETS would be appropriate to my problem, but I cannot figure out how to transform/reshape the resulting table from GROUPING SETS. This is my query using GROUPING…
green_C
  • 59
  • 1
  • 6
2
votes
1 answer

SQL - Order By Subtotal Count in a Grouping Set

This is my current statement: SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COUNT(*) COUNTER FROM TABLE GROUP BY GROUPING SETS ((COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5), COLUMN1); I have never used GROUPING SETS before so…
Delphy
  • 306
  • 1
  • 4
  • 16
2
votes
2 answers

Why could PostgreSQL 9.5's CUBE, ROLLUP and GROUPING SETS be slower than equivalent UNION?

I've anticipated new PostgreSQL 9.5 features very much, and going to upgrade our database very soon. But I was quite surprised when I found that SELECT col1, col2, count(*), grouping(col1,col2) FROM table1 GROUP BY CUBE(col1, col2) query on our…
codesnik
  • 299
  • 2
  • 7
2
votes
1 answer

Grouping sets columns in aggregate arguments and NULL replacement

There are many grouping sets examples on the internet like query Q1 in the example below. But query Q2 is different because A2 is a grouping column and it is used as the argument to SUM(). Which one of the following is correct for Q2 according to…
Ben C
  • 658
  • 6
  • 18
2
votes
1 answer

Data rows plus totals row in a single scan

Imagine we have following data in a table: groupName volume class mark ---------- ------- ------ ---- group1 50 1 o group1 50 1 o group1 50 1 x group1 25 2 o group2 25 1 …
i-one
  • 5,050
  • 1
  • 28
  • 40
1
vote
0 answers

MariaDB: How do I sort with grouping sets / rollup?

I have s set of data which I want to group with GROUPING SETS / ROLLUP. There is a fiddle at: https://dbfiddle.uk/XB0nOBWb . SELECT initial, data, count(*) FROM test GROUP BY ROLLUP(initial,data); Apparently MariaDB doesn’t have a grouping()…
Manngo
  • 14,066
  • 10
  • 88
  • 110
1
vote
1 answer

Using grouping sets in a custom function - R

I am working in R and I have the following data: data2<- data.frame(region_name = c("West", "West", "East"), type = c("small", "big", "big"), beta_rate = 7:9, gamma_rate = 4:6) I perform…
fe108
  • 161
  • 7
1
vote
1 answer

Combining OVER (PARTITION BY...) with GROUPING SETS?

I have the following transaction table: I would like to calculate the total quantity purchased for each: product category (i.e. total quantity of all products within the same category) department (i.e. total quantity of all products within the…
alhazen
  • 1,907
  • 3
  • 22
  • 43
1
2 3