Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    New York Metro

    Unanswered: Non aggregate in rollup


    I have a few columns I want to group together by cube, but I dont want the last two columns to be included as an aggregate.

    I tried the statement below, but it didnt give the desired result

    select sample1, count(*), sample2, sample3 not aggregate
    from <this table>
    group by rollup (sample1, sample2) , sample3

    However my counts for sample1 get messed up.

    Any suggestions?

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL

    Cool Maximize it

    You could try something like this:
      SELECT sample1,
             COUNT ( * ),
             MAX(sample3)    -- NOT   AGGREGATE
        FROM "<this table>"
    GROUP BY ROLLUP (sample1, sample2);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2010
    New York Metro
    Great idea, however is there a way to turn make to change that value when the row is a subtotal count?

    For instance, max(sample3) is "ABC"

    A1 1 A
    A2 1 AB
    A3 2 ABC <subtotal>

    Instead of ABC as the subtotal value, I would like to place something like "Total"

    I attempted to place a case statement in the select, however oracle complains that its not in the group by which doesnt accept case scenarios anyway.

    thank you for your input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts