CODEWITHSUNDEEP
sqloracle-databasecountsql-order-bygrouping-sets
Delphy
Delphy

Reputation: 316

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 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.

Upvotes: 2

Views: 549

Answers (1)

bbrumm
bbrumm

Reputation: 1352

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.

Upvotes: 1

Related Questions