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
ADVERTISEMENT
Aug 23, 2012
I have a table T with columns
Col1 Col2
1 A
2 B
3 C
4 D
[code]..
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3
1 A 1
2 B 1
3 C 1
4 D 1
5 D 2
6 A 2
[code]...
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.
View 3 Replies
View Related
Jul 25, 2013
I have a query find window that allows you to search on various attributes.Also i have a radio button within the query find that allows you to filter the results either as a single record or mulitiple records For example consider the data below.....
Header Table
Record_Id = 1, Record_No = 'Rec1'
Lines Table
Record_Id = 1, Line_desc = 'Line1'
Record_Id = 1, Line_desc = 'Line2'
Record_Id = 1, Line_desc = 'Line3'
I have a View that joins the 2 tables together so for Record_Id = 1 the view returns 3 rows
I would like to have a query find window that allows you to search using:
Record_No
Line_Desc
and has a radio button to allow you to either show the records as a single line or as all detail lines Therefore i would like the following selections:
1) Enter no search criteria but select Single radio option will return 1 record with default line description of Line1
2) Enter no search criteria but select Multiple radio option will return all 3 records
3) Enter Line_Desc = Line1 with Single radio option brings back one record with Line_Desc = Line1
4) Enter Line_Desc = Line2 with Single radio option brings back one record with Line_Desc = Line2
5) Enter Line_Desc = Line3 with Single radio option brings back one record with Line_Desc = Line3
6) Enter Line_Desc = Line1 with Multi radio option brings back one record with Line_Desc = Line1
7) Enter Line_Desc = Line2 with Multi radio option brings back one record with Line_Desc = Line2
7) Enter Line_Desc = Line3 with Multi radio option brings back one record with Line_Desc = Line3
I need the form to select from the view but then perform a rank after it has selected the relavant data. Then the radio button would use the ranking to select either one record or multi records.
View 4 Replies
View Related
Jun 29, 2011
I have a table t
CREATE TABLE T
(ID NUMBER(4),
NAME VARCHAR2(40))
INSERT INTO T VALUES (1,'JAMES');
INSERT INTO T VALUES (1,'DOLLY');
INSERT INTO T VALUES (2,'MICHEAL');
INSERT INTO T VALUES (2,'FLASH');
INSERT INTO T VALUES (3,'JAMES');
INSERT INTO T VALUES (3,'MARY');
INSERT INTO T VALUES (4,'JAMES');
INSERT INTO T VALUES (4,'DOLLY');
INSERT INTO T VALUES (5,'JAMES');
INSERT INTO T VALUES (5,'DOLLY');
INSERT INTO T VALUES (6,'JAMES');
INSERT INTO T VALUES (6,'MARY');
SELECT * FROM T ORDER BY 1
ID NAME
1 JAMES
1 DOLLY
2 MICHEAL
2 FLASH
3 JAMES
3 MARY
4 JAMES
4 DOLLY
5 JAMES
5 DOLLY
6 JAMES
6 MARY
each 'ID' has two values always. I want to rank the data based on same pair 'name' in an 'ID'
for example, my desired output is:
ID NAME RANK
1 JAMES 1
1 DOLLY 1
2 MICHEAL 1
2 FLASH 1
3 JAMES 1
3 MARY 1
4 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
4 DOLLY 2 -----> SAME AS ABOVE
5 JAMES 3 ---> THAT IS RANK 2 BECAUSE THIS IS THE 3RD TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
5 DOLLY 3 -----> SAME AS ABOVE
6 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND MARY ARE IN THE SAME 'ID'
6 MARY 2 -----> SAME AS ABOVE
I want the output in exactly above format.
View 3 Replies
View Related
Jul 4, 2012
Lets say I have a table like the following -
ID--------DATE_TIME-----------------VALUE
101----- 01/01/2012 14:00:00 ---12
101----- 11/01/2012 23:00:00 ---17
101----- 13/01/2012 10:00:00 ---22
101----- 19/03/2012 08:00:00 ---7
101----- 19/03/2012 19:00:00 ---7
101----- 19/03/2012 20:00:00 ---7
101----- 20/03/2012 02:00:00 ---3
101----- 20/03/2012 03:00:00 ---3
101----- 21/03/2012 13:00:00 ---14
101----- 21/03/2012 14:00:00 ---14
101----- 21/03/2012 21:00:00 ---13
101----- 21/03/2012 22:00:00 ---13
101----- 21/03/2012 23:00:00 ---13
101----- 22/03/2012 00:00:00 ---13
I'm looking for a script to partition the data into sections where the VALUE is the same over a constant period of time with no breaks. I'd like to give each partition a value to identify it by.
So the outcome of the script would be the following -
ID--------DATE_TIME-----------------VALUE-----IDENTIFIER
101----- 01/01/2012 14:00:00 ---12----------1
101----- 11/01/2012 23:00:00 ---17----------2
101----- 13/01/2012 10:00:00 ---22----------3
101----- 19/03/2012 08:00:00 ---7------------4
101----- 19/03/2012 19:00:00 ---7------------5
101----- 19/03/2012 20:00:00 ---7------------5
101----- 20/03/2012 02:00:00 ---3------------6
101----- 20/03/2012 03:00:00 ---3------------6
101----- 21/03/2012 13:00:00 ---14----------7
101----- 21/03/2012 14:00:00 ---14----------7
101----- 21/03/2012 21:00:00 ---13----------8
101----- 21/03/2012 22:00:00 ---13----------8
101----- 21/03/2012 23:00:00 ---13----------8
101----- 22/03/2012 00:00:00 ---13----------8
I was trying to do something with trunc(date_time) but that didnt work out right as the blocks of data can carry over several days as seen in the rows with IDENTIFIER = 8.
View 7 Replies
View Related
Aug 30, 2012
I have the following table :
CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(7,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(9,2,3,4);
[code]....
I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);I would like to use the rank function.
Desired update:
A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4
View 3 Replies
View Related
Jul 17, 2008
I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.
Table description:
document_master_duplicates
(
documentid,
duplicate_setid,
is_master
)
This needs to join to another table briefcase_documents which contains our set of documents. The briefcase / document relationship is many-to-many.
Table description:
briefcase_documents
(
briefcaseid,
documentid
)
There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.
Here's an example set that I would want to order by:
DocumentId Page_Count SetId Is_Master
2002 2 1 0
2003 20 2 0
2008 20 NULL NULL
2010 20 4 0
2012 1 4 1
2001 5 1 1
2004 16 3 1
2011 17 4 0
2014 10 5 0
2009 9 NULL NULL
As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.
The result i'm looking to achieve will be ordered as follows:
DocumentId Page_Count SetId Is_Master
2012 1 4 1
2011 17 4 0
2010 20 4 0
2001 5 1 1
2002 2 1 0
2004 16 3 1
2009 9 NULL NULL
2014 10 5 0
2003 20 2 0
2008 20 NULL NULL
As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.
With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff?
View 10 Replies
View Related
Apr 14, 2012
I have a table with numbers like this in a column
sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
my desired output is:
sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.
View 3 Replies
View Related
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
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
View Related
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
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
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
Jul 3, 2010
how to find cumulative total means i need total amount between april to sysdate.
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
