Search code examples
sqloracle-databaseoracle9i

Using the ROLLUP total within a query

Table Costs:

Item | Cost
 Car |  10
 Cat |   2
 Dog |   3
 Fish|   1

Is it possible to use the sum within a per-row query?

SELECT Item, Cost, Cost/sum(cost) "Percent" from Costs where ...

to get:

Item | Cost | Percent
 Car |  10  | 0.625
 Cat |   2  | 0.125
 Dog |   3  | 0.1875
 Fish|   1  | 0.0625

This is just a simple example. My query is a lot more complicated, so I'd rather not do another full table scan to get a "select sum(cost) where ...". Would it possible to do this using grouping/rollup?

Solution

  • You can use RATIO_TO_REPORT function to get the ratio, without calculating the sum.

    SQL Fiddle

    Query 1:

    select item,
       cost,
       ratio_to_report(cost) over () as percent
from costs

    Results:

    | ITEM | COST | PERCENT |
|------|------|---------|
|  car |   10 |   0.625 |
|  cat |    2 |   0.125 |
|  dog |    3 |  0.1875 |
| fish |    1 |  0.0625 |