| Oracle SQL Scripts: |
| 1. Convert numeric values to words (useful for Cheque/Check printing). Note: "Jsp" can be used to INITCAP the words |
| 2. Use of ROLLUP() function in summary queries |
| 3. Use of ROLLUP() function in summary queries to compute Percentages (%) |
| Here is a tip to compute totals and percentage using GROUPING and ROLLUP function in summary queries. To get the correct percentage, the WHERE clause in runtime table (designated with alias "ts" below) should match the WHERE clause of the query. The output can be formatted as necessary to show the summary column details (totals). |
| Note: There can be an n-th decimal offset due to the use of ROUND(m,n) function, but the total value will always equal sum of all the grouped columns without rounding. |
| Output of the above query: | ||||
| : | ||||
| SALES_DETAILS | DIVISIONS | REC_COUNT | SALES_$$ | Area % |
| US EAST | 27 | 41 | 4921983 | 54.86 |
| US WEST | 6 | 37 | 4049139 | 45.14 |
| Total .................. | 33 | 78 | 8971122 | 100.00 |
| 4. Use of GROUPING_ID() with ROLLUP() function in summary queries GROUPING_ID() with ROLLUP() function |
| For corrections or feedback, other useful links please contact webmaster |