SQL & PL/SQL :: How To Find Cumulative Total
Jul 3, 2010how to find cumulative total means i need total amount between april to sysdate.
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: Cumulative Total - Passing Parameter Value
Mar 27, 2012i have a table empl which have three column (name,period,attendance)if we pass parameter which value is based on period column
like :January
then out should come sum of all attendence of january group by name like that
name attendance
a60.00
b20.00
c20.00
w40.00
if we pass parameter value :February then attendance should come sum of (January and February) and if we pass parameter March then attendance should come sum of(January,February and march)
create table empl (name varchar2(10),period varchar2(10),attendance number)
insert into empl values('a','January',20)
insert into empl values('a','January',10)
insert into empl values('a','January',30)
insert into empl values('a','February',20)
insert into empl values('a','March',60)
insert into empl values('b','January',20)
insert into empl values('c','January',20)
insert into empl values('w','January',40)
SQL & PL/SQL :: Find Total Sales For The Whole Last Week
Mar 7, 2010I have two columns in table
sales_date
sales_amount
I need to find total sales for the whole last week.
Today is current week i.e. 1 (March 1, 2010 and March 7, 2010)
I need to find total_sales for the last week (i.e. Feb 22, 2010 to Feb 28, 2010)
I am unable to create logic for the same.
Find Total Of All Active People For Each Region Code
Jun 18, 2012I would like to run a query that counts by case_manager, number of distinct app_id's that have a status ='AC' in a select number of programs. All of the fields are in the same table.I want it to look like this:
Case MgrA DYYOY
Jane13420
John3452
Alice1233
Fields are case_manager, status, applicant, and program table reg...I can do the count command to find the total of all active people for each region code. What I want it for the breakdown by program the people are in.
My query for that is:
SELECT case_manager, Count (*)
from reg
where status='AC'
Group by case_manager
order by case_manager
and I get this:
Case ManagerCount *
Jane46
John 14
Alice9
SQL & PL/SQL :: Find Total Space Occupied On Disk By Tablespaces Of Database
Nov 3, 2010I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name;
The output of the query is as shown in the attached file:
TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...
Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB,
sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace"
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name ;
[code]...
Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.
tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.
Reports & Discoverer :: How To Find Page Number And Total Number Of Pages
Jan 26, 2010i am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
SQL & PL/SQL :: Cumulative Some Last 5 Rows?
Jun 25, 2013I need to get the sum of next 5 rows that are order by one specific column .
Ex :
DROP TABLE TEST_5;
CREATE TABLE TEST_5 AS
SELECT TO_NUMBER(200 || ROWNUM) YEAR, ROWNUM TOT
FROM ALL_OBJECTS
WHERE ROWNUM < = 15 ;
SQL> select * from test_5 order by year;
YEAR TOT
---------- ----------
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10
20011 11
20012 12
20013 13
20014 14
20015 15
15 rows selected
SQL>
Now Expected Results :
SQL> select * from test_5;
YEAR TOT Exp_res
---------- -----------------------
2001 1 1+2+3+4+5 = 15
2002 2 2+3+4+5+6 = 20
2003 3 3+4+5+6+7 = 25
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10 10+11+12+13+14
20011 11 11+12+13+14+15
20012 12 12+13+14+15
20013 13 13+14+15
20014 14 14+15
20015 15 15
15 rows selected
SQL>
SQL & PL/SQL :: Cumulative Sum Of Previous Row In Same Column?
Jun 28, 2013Is it possible to get cumulative sum of the same column? I am trying to get a value for COL6... it is dependent on the values of previous row
COL6 Formula:
IF COL2 = 'A' THEN
IF 100 - [SUM_COL6] > COL5 THEN
COL5
ELSE
(TRUNC(100 - [SUM_COL6] / COL4) )* COL4
END IF
[code]....
SQL & PL/SQL :: Cumulative Calculation In Oracle?
Feb 24, 2011Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:
Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.
SQL & PL/SQL :: Last 3 Receipts Based On Cumulative Balance?
Mar 19, 2011I have one table where the item stock balance can be taken.What i want is like a ageing report based on the ending balance.If the ending balance is 10,000 for item a.It should bring all the Receipts which have qty lesser than 10,000.
I managed to write one function like below to get the receipts numbers but the format is 3778578-3778612-3790428 and i want to convert them into rows like
3778578
3778612
3790428
The function used is as below.
CREATE OR REPLACE FUNCTION l_get_rcpt (
l_item IN VARCHAR2,
l_date DATE,
l_bal IN NUMBER
)
[code]...
SQL & PL/SQL :: Cumulative Running Balance By Condition?
Mar 24, 2013Is there a way to find running balance for accounts, suppose i have two tables.one for transaction and one summary of balance.
i want to have the closing balance based on transactions subtracted or added based on credit and debit for example.it will add to the running total if its debit and subtract if its credit and final output is the balance.
create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);
insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','03-FEB-2013',0,100);
insert into acnt_trans values ('1001','04-FEB-2013',50,0);
[code]....
--I want the output as below.if i run the report as of '04-feb-2013'
trans_date , acnt_code , debit_amt,credit_amt , balance
02-feb-2013 1001 200 0 3200
03-feb-2013 1001 0 100 3100
04-feb-2013 1001 50 0 3150 -- this is the closing balance.
PL/SQL :: Total And Grand Total
Mar 26, 2013I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.
SELECT decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
--decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
farinva_invh_Code,
spriden_id,
--spriden_last_name "last Name"
[Code]....
SQL & PL/SQL :: Running Total SUM (Amt) Over?
Apr 21, 2012My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.
On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.
Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30
[code]...
My imaginary SQL would look something like at this point (if I have it right):
SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable
How to get the last column AmtLeft?
PL/SQL :: Ranking Sum Total But Not Overall?
Sep 7, 2012I'm trying to Rank Username based on the Total Sum of amount waived but I want to avoid Ranking the Overall Total at the bottom, plus I dont want them in Ranking order, I want the order to stay the same as it currently is.
SELECT DECODE(GROUPING(USERNAME),1,'TOTAL',0,UPPER(USERNAME)) as "USERNAME",
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JAN' THEN AMOUNT_WAIVED ELSE 0 END) AS JAN,
SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'FEB' THEN AMOUNT_WAIVED ELSE 0 END) AS FEB,
[code]....
Getting Total Results Count
Aug 27, 2010I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
Total At Bottom Of Group
Jul 8, 2009I am writing a report that breaks on the first 4 fields. That part is working fine. I also want a count for each Group (the 4 fields), and a grand total. Since I want to break on all 4 fields as if they are one combined field, I made a concatenated column (called Break_key) and had the report total on that.
I was surprised when the count appeared at the top of each group, rather than at the bottom. The grand total is at the very bottom of the report, as I would have thought. How can I get the sub-totals at the bottom, rather than the top?
Report Builder 9.0.4.0.33
My query:
select distinct
fs.FUNDING_SOURCE ,
lpad(l.circuit, 2, '0') circuit,
l.DEPARTMENT ,
l.REIMB_TYPE ,
p.POSITION_NO ,
decode (p.position_type, 'F', 'Full', 'S', 'Shared') position_type ,
ph.phasein,
[Code]....
SQL & PL/SQL :: How To Get Total Object Privileges
Jun 1, 2010Is there any data dictionary table to get the object grants for total objects in the database?
Using DBA_TAB_PRIVS, i get object grants for tables only. But i'm looking for remaining objects also. The list of remaining objects is below.
view, or materialized view
Sequence
Procedure, function, or package
User-defined type
Synonym for any of the preceding items
Directory, library, operator, or indextype
Java source, class, or resource
provide me the other data dictionary tables for querying.
SQL & PL/SQL :: Total Count Per Day Query?
May 31, 2013 I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:
SELECT sum(duration),sum(stlmntcharge)
FROM voipcdr
WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD'))
AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))
or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')
AND direction = 'I'
ORDER BY calldate, calltime;
SQL & PL/SQL :: Total Of Count Query
Apr 29, 2010i need the total of the count query I am executing.My query is
SELECT COUNT(*) FROM po_headers WHERE
CREATION_DATE BETWEEN '01-MAR-2010' and '31-MAR-2010' GROUP BY VENDOR_ID
this gives output as
6
1
3
4
My objective is to find the total of distinct vendors for a given date range.how should I modify this query.
SQL & PL/SQL :: Getting Total And Single Name In One Query?
Oct 29, 2013I want to get any employee name of deptno 10 but total count of number of employees under dept 10.
DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;
SQL & PL/SQL :: Total Sum Of Prices At The Bottom
Oct 8, 2010I am displaying customers names and there inventory prices(inv_price * ol_quantity) however I need a total sum of all customer prices at the bottom
ex
name1 | 50
name2 | 25
total amount | $75
DECLARE
CURSOR abc IS
SELECT customer.c_last, SUM(inventory.inv_price * order_line.ol_quantity) AS Total,
inventory.inv_price, order_line.ol_quantity
FROM customer, orders, order_line, inventory
WHERE customer.c_id = orders.c_id AND
[Code]...
I tried SUM(Total) but it can't use that
PL/SQL :: Running Total For Each Day - Nulls?
Oct 2, 2013I'm on 11.2.0.3. I want to write a query to get calculate a running total of incidents per day - this query will be used for an APEX line chart.Sample table and data:
create table sales (
id number primary key,
time_of_sale date,
item varchar2(20));
insert into sales values (1, to_date('02-JAN-2013','DD-MON-YYYY'), 'book'); ....
PL/SQL :: Rollup Grand Total?
Aug 8, 2013I am trying to use rollup for a total, but it doesn't work because what I'm totaling is too conditional. I even tried adding on the totals to the end using a union and a specialized function, but for some reason, the order by stops working when I do the union (all field aliases and types are the same).I know the structure of the query itself probably isn't the best, how I can get the grand total, The subtotals from the rollup work fine, though.
The order by query runs fine against both parts of the union when they are run individually, but once I make it part of the union - the first part of the order by is an unknown field and the second is not a SELECTed list expression. I've commented out the union here. The statement works fine without it except for the grand total being all 0s. I kind of understand why it's all 0s, but not how to get around that.This is running in Oracle 10g2. I am trying to get totals for decisions by each of the four component values: ex_land, ex_imp,tx_land,tx_imp and then subtotals for each component values.
That works for the following query, but I cannot get a grand total through the rollup.
CREATE TABLE "FL_APP" ( "APP_ID" NUMBER(*,0), "FL_STAT_CODE" VARCHAR2(20 BYTE), CONSTRAINT "FL_APP PK" PRIMARY KEY ("APP_ID") ENABLE, CONSTRAINT "FL_APP_FL_STAT_DIM_FK1" FOREIGN KEY ("FL_STAT_CODE") REFERENCES "FL"."FL_STAT_DIM" ("FL_STAT_CODE") ON DELETE CASCADE ENABLE ) ;CREATE TABLE "FL_APP_PROP" ( "APP_ID" NUMBER(*,0), "FL_DECISION_CODE" VARCHAR2(10
[code]....
Reports & Discoverer :: GP Percentage In Total
Jan 10, 2012See the attached pdf the output of my report. I want to calculate the GP at the Total column.
My GP formula is
trunc((profit) / decode(ACTUAL,0,1,ACTUAL)*100,2).
I tried using Placeholder Column & Formula Column but it is not working. I am using grouping in INV_DATE and summing all the columns.
Forms :: Sum Total Quantity On Form
Nov 8, 2011I have problem about sum record on form.
This is records display on form.
NO Merchandise Slip Quantity
------------------------------------------------
152501B002CAH2-002980016
162501B002CAH1-003528716
142501B002CAH1-003529812
122501B002CAH2-002979316
132501B002CAH2-002979612
202501B002CAH1-003529612
212501B002CAH1-003529716
192501B002CAH2-002979516
172501B002CAH2-002978716
182501B002CAH1-003529116
112501B002CAH1-003529212
42501B002CAH1-003530012
52501B002CAH2-002978916
[Code]....
Now, i want to count quantity follow slip_no and merchandise.
Example:
Merchandise :2501B002CA have 8 slip with quantity is 12 and 5 slip with quantity is 16.
Merchandise: 2501B001CA have 6 slip with quantity is 12 and 7 slip with quantity is 16.
Detail you can see file attach
-------
But i don't know how to do for result same above.
Forms :: Total Field From Another Block
Jun 14, 2013I have 2 blocks named emp,emp1 those are DB's Blocks and empno, ename, sal in EMP block and sal, sum(sal) are in EMP1 block, i set the property for sum is summarized block is: EMP1 and item is SAL, i want display sum(sal), but not display when i click execute query.
View 5 Replies View RelatedForms :: How To Get Total Records And Amount
Jul 16, 2013I've a form that allow the user to enter information & save it. in the form there is text_item called AMOUNT with multiable records.
I want when the user press SAVE button, a popup message shows the number of records enterd & total amount in all records.
EX:
AMOUNT
1000
200
3000
After clicking Save button a popup message shows ( you enterd 3 records & 4200 $ ) OK ? Cancel?
this my code in WHEN-BUTTON-PRESSED trigger
if :amount IS NOT NULL THEN
declare
cnt_record number := 1;
cnt_amount number :=:amount;
begin
go_item('amount');
[code]....
BUT !! when I test the form nothing happened.
SQL & PL/SQL :: Rows Depends On A Total Field
Feb 24, 2012I have a table TP having following data (Dashes used for space as i am unable to have proper alignment)
ID1-----TOT
1 ------- 5
2 ------- 7
I need a query that repeat the records depending on the field TP.TOT
e.g.
ID1 -----TOT
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
Forms :: Calculation Total In Application?
Mar 16, 2011I'm getting trouble in getting the sum of all the product's prize present in the form fields of invoice form. For example, I user bought 2 products so I want to get there prize sum in the total box. I first want that I've QUANTITY, DISCOUNT and SELLING PRIZE.
If I multiply Quantity with Selling Prize then I gets the result in Product and Quantity Total Box. However, Now I want to add discount option in it. Means if user is providing 10% discount to his/her customer then Quantity * Selling Prize - Discount% becomes a result in product's solution final prize box.
Here is the pic..
Look...Now here..I've added 22 in quantity.. now if I'll insert 10% in discount then the total will comes in Amount Display Box.
I'm currently using this coding on on Prize Box..
:SP_PRODUCT_PTOTAL := NVL(:SP_PRODUCT_QUANTITY,0) * NVL(:SP_PRODUCT_PRIZE,0) * NVL(:SP_PRODUCT_DST,0) / 100;
SQL & PL/SQL :: Bulk Delete With Total Count
Feb 24, 2009I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.
I cannot run the below code considering the amount of records or rows in the table A.
Begin
Delete from A where trunc(date_column) < '01-FEB-2009';
commit;
End;
where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?