SQL - Order By Subtotal Count in a Grouping Set

Question

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 I'm not sure if I am using them correctly but my output looks right. But what I am missing is I want to order by my subtotal COUNTER and then by my COLUMN1. My current output is as below:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COUNTER
TEST0   TEST1   TEST3   TEST3   TEST4   1
TEST0                                   1
TEST    TEST    TEST    TEST    TEST    1
TEST    TEST1   TEST1   TEST1   TEST1   1
TEST    TEST2   TEST2   TEST3   TEST4   1
TEST                                    3
TEST2   TEST3   TEST4   TEST5   TEST6   1
TEST2                                   1

The output I would like is:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COUNTER
TEST    TEST    TEST    TEST    TEST    1
TEST    TEST1   TEST1   TEST1   TEST1   1
TEST    TEST2   TEST2   TEST3   TEST4   1
TEST                                    3
TEST0   TEST1   TEST3   TEST3   TEST4   1
TEST0                                   1
TEST2   TEST3   TEST4   TEST5   TEST6   1
TEST2                                   1

Ordered by the subtotal COUNTER greatest to smallest and then by COLUMN1.

The doc is actually quite good in this area. I think you want to look at GROUPING_ID and GROUP_ID on context of GROUPING SETS. Take a look here: http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8627 — BobC, Mar 01 '17 at 18:14

bbrumm · Accepted Answer · 2017-03-02T00:17:05.540

1

You could use the MAX function as an analytic function to order your results.

SELECT
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COUNTER
FROM (
    SELECT
    COLUMN1,
    COLUMN2,
    COLUMN3,
    COLUMN4,
    COLUMN5,
    COUNTER,
    MAX(counter) KEEP (DENSE_RANK FIRST ORDER BY counter DESC) OVER (PARTITION BY first_name) AS maxcount
    FROM (
      SELECT
      COLUMN1,
      COLUMN2,
      COLUMN3,
      COLUMN4,
      COLUMN5,
      COUNT(*) AS COUNTER
      FROM
      TABLE
      GROUP BY GROUPING SETS ((COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5), COLUMN1)
    )
)
ORDER BY maxcount DESC, COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5;

You can also change the ORDER BY to adjust how you want the results ordered.

I've just written an article about GROUP BY and related functions last week which might be helpful: Oracle GROUP BY – The Complete Guide. I'll have to update it to add this ordering functionality.

edited Mar 02 '17 at 00:17

answered Mar 01 '17 at 18:45

bbrumm

1,342
1
8
13

When I try to use the ORDER BY GROUPING_ID((COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5), COLUMN1) line I get an error that I am missing a right parenthesis after the first mention of COLUMN1. Could you help me with this? – Delphy Mar 01 '17 at 23:19
Sure, I've updated the query. Looks like there was an issue with GROUPING_ID and nested brackets, so I've used a subquery. Does this work for you now? – bbrumm Mar 01 '17 at 23:35
That seems to have just put all my subtotals at the top together. I would want it by COUNTER but keeping the grouped lines together. – Delphy Mar 01 '17 at 23:45
I understand. I've updated my query to use MAX as an analytic function. Does this give you what you want? It looked correct on my sample data. – bbrumm Mar 02 '17 at 00:17
That worked! Never used a lot of those functions! Thanks for the help! – Delphy Mar 02 '17 at 00:41

SQL - Order By Subtotal Count in a Grouping Set

1 Answers1