| Place Holder in SQL query [message #1228] |
Fri, 12 April 2002 09:16  |
Bobby
Messages: 32
Registered: August 2000
|
Member |
|
|
Hi
I want to calculate a running balance in SQL query ..eg
select amt, (obal+amt) from table..
obal is taken from a function and the running balance needs to be calculated..
Thanks in advance
|
|
|
|
| Re: Place Holder in SQL query [message #1263 is a reply to message #1228] |
Mon, 15 April 2002 11:19  |
obie-wan
Messages: 5
Registered: April 2002
|
Junior Member |
|
|
To get subtotals and totals in a single SQL statement is done using the ROLLUP, GROUPING and CUBE functions. The ROLLUP and CUBE functions are used with the GROUP BY clause and the GROUPING function can be used in the main query to determine if the row returned is a sub-total or a total.
SELECT component_type component,bus_unit "Business Unit",COUNT(*) AS amount
FROM installed_component
GROUP BY ROLLUP(component_type,bus_unit)
HAVING COUNT(*) > 100;
The last row returned is the grand total for the amount column. To change the NULL to something meaningful, the GROUPING function can be used with the DECODE. If the value output is 1 then the row was generated using the ROLLUP function.
SELECT DECODE(GROUPING(component_type),1,'Grand Total',component_type) component,
bus_unit "Business Unit",COUNT(*) AS amount
FROM installed_component
GROUP BY ROLLUP(component_type,bus_unit)
HAVING COUNT(*) > 100;
Use the CUBE function for subtotals.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
