Oracle group data

I have table, MyTable, in Oracle 11GR2:

COL1 VARCHAR2
COL2 VARCHAR2
COL3 VARCHAR2
COL4 NUMBER

with data

COL1 COL2   COL3    COL4
A    B      C       1
A    D      E       2
F    G      H       3
F    I      J       4
K    L      M       8

How do I get results with SQL:

COL1        COL2    COL3    COL4
K           L       M       8
K           Total           8
F           I       J       4
F           G       H       3
F           Total           7
A           D       E       2
A           B       C       1
A           Total           3
Grand Total                18

I tried with roolup function:

SELECT m.col1, m.col2, m.col3, sum(m.col4)
FROM MyTable m
group by rollup(m.col1, (m.c开发者_Python百科ol2, m.col3))

I don't know how to sort to get results sorted by col4 like example above. Thanks

I don't use ROLLUP often, but the difficulty seems to be that once the ROLLUP is applied, there is no connection between the summary rows and the detail rows, so you can't easily sort them as groups within the overall result set, which is what I think you want to do.

I think this will get what you want, but it could be inefficient on large data sets; it essentially calculates the subtotal values twice, once with rollup, and once with an analytic function.

select * 
  from (select m.col1, 
               m.col2, 
               m.col3, 
               sum(m.col4) sum_c4
          from mytable m
        group by rollup(m.col1, (m.col2, m.col3))
       )
order by case when col1 is null then 1 
              else 0 
         end asc,                                     -- put grand total line at end of entire set
         sum(sum_c4) over (partition by col1) desc,   -- sort subgroups by descending subtotal
         col1,                                        -- tiebreaker for subgroups with same total
         case when col2 is null then 1 
              else 0 
         end asc,                                     -- put subtotal line at end of each group
         sum_c4 desc                                  -- put detail lines in descending order

There are three functions that provide information about the superaggregate rows that are returned. These functions are group_id grouping and grouping_id.

I think the one you want to use here is grouping. This function will tell you if it is summed on a given column. Calling the function for superaggregate rows will return 1 and other non-superaggregate rows will return 0. Details on the grouping function can be found in the Oracle documentation.

The query that you want is shown below:

select case when grouping_col1 = 1 then 'Grand Total'
            else col1
       end as col1,
       case when grouping_col1 = 0 and grouping_col2 = 1 then 'Total'
            else col2
       end as col2,
       col3,
       sum_col4,
       grouping_col1,
       grouping_col2
  from (select col1,
               col2,
               col3,
               sum(col4) as sum_col4,
               grouping(col1) as grouping_col1,
               grouping(col2) as grouping_col2
          from mytable
        group by rollup(col1, (col2, col3))
       )
order by grouping_col1,
      sum(sum_col4) over (partition by grouping_col1, grouping_col2, col1) desc,
      grouping_col2,
      sum_col4 desc

Append

ORDER BY sum(m.col4) DESC

to your query.

you can run following query -

SELECT COL1 ,COL2,   COL3,    COL4 FROM
(
    SELECT  COL1 ,COL2,   COL3,    COL4 FROM MyTable
    UNION ALL 
    SELECT COL1,'Total', NULL, SUM(COL4) FROM MyTable
    GROUP BY COL1
    ORDER BY 1,3 -- line to change for case discussed below
)
UNION ALL
SELECT 'GRAND TOTAL',NULL,NULL,SUM(COL4) FROM MyTable

I have tested above query on my machine and it is working fine.

By any chance if you will get 'Total' row before main table's row then edit last line as -

ORDER BY 1,3 DESC