SQL & PL/SQL :: Running Total SUM (Amt) Over?

Apr 21, 2012

My 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?

View 14 Replies


ADVERTISEMENT

PL/SQL :: Running Total For Each Day - Nulls?

Oct 2, 2013

I'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'); ....

View 6 Replies View Related

PL/SQL :: Running Total For Duplicate Rows

Dec 19, 2012

I am trying to write an sql which shows the running total for records which has duplicate.

sample rows:

col1 col2 col3
1      A    2
1      A    2
1      A    2
1      B    3
1      B    3
1      C    5
1      D    2
1      D    2o

p required:

col1 col2 col3  cumulative_tot
1      A    2       2
1      A    2       2
1      A    2       2
1      B    3       5
1      B    3       5
1      C    5       10
1      D    2       12
1      D    2       12

View 3 Replies View Related

SQL & PL/SQL :: Monthly Running Total With Opening Balance

May 9, 2012

I want to generate the gapless montly running balance for the whole year.Data in the table "A" is as below.

code Date amount remarks
0101 30-SEP-10 1000 Opening
0101 01-OCT-10 1500
0102 25-OCT-10 500
0102 10-DEC-10 750
0101 26-JAN-11 450
0102 03-MAR-11 3000
0101 30-SEP-11 6000

Required output is
Code Date amount Balance
0101 31-OCT-10 1500 2500
0101 30-NOV-10 0 2500
0101 31-DEC-10 0 2500
0101 31-JAN-11 450 2950
-- -- -- continue
0101 30-SEP-11 6000 8950

Same output for code 0102. I have taken only 02 code for excample only. There are about 250 code in table.

View 14 Replies View Related

PL/SQL :: Total And Grand Total

Mar 26, 2013

I'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]....

View 3 Replies View Related

PL/SQL :: Ranking Sum Total But Not Overall?

Sep 7, 2012

I'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]....

View 8 Replies View Related

Getting Total Results Count

Aug 27, 2010

I 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?

View 4 Replies View Related

Total At Bottom Of Group

Jul 8, 2009

I 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]....

View 2 Replies View Related

SQL & PL/SQL :: How To Get Total Object Privileges

Jun 1, 2010

Is 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.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Cumulative Total

Jul 3, 2010

how to find cumulative total means i need total amount between april to sysdate.

View 1 Replies View Related

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;

View 6 Replies View Related

SQL & PL/SQL :: Total Of Count Query

Apr 29, 2010

i 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.

View 1 Replies View Related

SQL & PL/SQL :: Getting Total And Single Name In One Query?

Oct 29, 2013

I 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;

View 7 Replies View Related

SQL & PL/SQL :: Total Sum Of Prices At The Bottom

Oct 8, 2010

I 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

View 2 Replies View Related

PL/SQL :: Rollup Grand Total?

Aug 8, 2013

I 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]....

View 8 Replies View Related

Reports & Discoverer :: GP Percentage In Total

Jan 10, 2012

See 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.

View 22 Replies View Related

Forms :: Sum Total Quantity On Form

Nov 8, 2011

I 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.

View 7 Replies View Related

Forms :: Total Field From Another Block

Jun 14, 2013

I 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 Related

Forms :: How To Get Total Records And Amount

Jul 16, 2013

I'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.

View 5 Replies View Related

SQL & PL/SQL :: Rows Depends On A Total Field

Feb 24, 2012

I 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

View 5 Replies View Related

Forms :: Calculation Total In Application?

Mar 16, 2011

I'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;

View 19 Replies View Related

SQL & PL/SQL :: Bulk Delete With Total Count

Feb 24, 2009

I 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?

View 14 Replies View Related

SQL & PL/SQL :: Cumulative Total - Passing Parameter Value

Mar 27, 2012

i 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)

View 22 Replies View Related

SQL & PL/SQL :: Find Total Sales For The Whole Last Week

Mar 7, 2010

I 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.

View 4 Replies View Related

Server Administration :: Total No Of Files In FRA

Jul 16, 2011

From the view v$flash_recovery_area_usage, below output shows total no of files are 24+4=28.

FILE_TYPE NUMBER_OF_FILES
------------ ---------------
CONTROLFILE 0
ONLINELOG 0
ARCHIVELOG 24
BACKUPPIECE 4
IMAGECOPY 0
FLASHBACKLOG 0

From the view v$recovery_file_dest, it shows the no of files are 49.

NAME NUMBER_OF_FILES
------------ ----------------
/dba/backup 49

Why there is discrimination?

View 1 Replies View Related

SQL & PL/SQL :: Adding Grand Total In Result

Jun 21, 2010

i just want to add at the end of each area_cd the total number of asset.. what do you think i should add in my query????

View 32 Replies View Related

SQL & PL/SQL :: How To Show Total Number Of Department

Apr 19, 2013

how to show total number of department with their department name assign to employee table.

View 2 Replies View Related

SQL & PL/SQL :: Sum Daily Total To Weekly In Oracle?

Feb 17, 2010

I need to sum the below daily total to weekly with the week starting day of Saturday Jan 3 2009 for the 52 weeks in 2009. The below query provides me the weekly total with the week starting day MONDAY. But I need the week starting day to be SATURDAY instead of MONDAY.

select to_char(report_date, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date, 'YYYYIW')

Here is a query I used to generate the Daily Sample Data:

SELECT DISTINCT A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD') as post_date,'RCSL', A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT) as Amount_Posted
FROM A, B, C
AND B.BEGIN_DT BETWEEN TO_DATE('&StartDate','YYYY-MM-DD') AND TO_DATE('&EndDate','YYYY-MM-DD')
GROUP BY A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD'), A.DEPTID, A.ACCOUNT

For the below sample data I would need to add data starting 2010-01-02 - 2010-01-08 to get my weekly total.

7777,2010-01-01,RCSL,0170,331062,-85
7777,2010-01-02,RCSL,0170,331063,-190
7777,2010-01-03,RCSL,0170,341413,170.18
7777,2010-01-04,RCSL,0170,347103,-880
7777,2010-01-05,RCSL,0172,331050,-116
7777,2010-01-06,RCSL,0172,331053,-50
7777,2010-01-07,RCSL,0172,331061,-63
7777,2010-01-08,RCSL,0172,331061,-63

View 16 Replies View Related

SQL & PL/SQL :: Total Records Count Calculation

Jan 14, 2013

We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .

View 7 Replies View Related

SQL & PL/SQL :: Query To Get Total Points From Various Factors

Oct 20, 2011

I am trying to calculate the total points of each customer

create table Customers(Customer_no number,revenue varchar2(30),Regularity varchar 2(30)
);
insert into Customers values(1,'25','2');
insert into Customers values(2,'50','3');

Table customers:

Customer_no, revenue, regularity
1,25,2
2,50,3

create table Points(ponts ID number,Factor varchar2(30),Weight number);

insert into Points values(1,revenue,2);
insert into Points values(1,Regularity,4);

Table points
Points ID,Factor,Weight
1,revenue,2
1,regularity,4

So for customer_no: 1

revenue is 25 * 2(weight for revenue ) =50
regularity is 2*4(weight for regularity) =8
Total =58

So for customer_no: 2

revenue is 50 * 2(weight for revenue ) =100
regularity is 3*4(weight for regularity) =12
Total =104

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved