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


ADVERTISEMENT

SQL & PL/SQL :: How To Print All Prime Factors Of Given Number

Sep 3, 2012

print the list of prime factors of a given number create or replace FUNCTION prime (num in number)

return sys_refcursor
as
i number := 2;
temp number := num;
temp_cur sys_refcursor;
BEGIN
open temp_cur;

[code]....

Quote:gives error -->>expression is of wrong type @ open temp_curur;

how to print all the prime factors of a given number??

View 9 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

PL/SQL :: Query To Total Attendance By Months

Jul 21, 2012

I need the following totals for each student based on the schools that they are enrolled for each month July through June.

Total Days Suspended from each school

Attendance Table Columns - Absent Date, Absent Code, Pupil_Number, School (Absent Codes for suspended = 3)

Total Days Present at each school - Days Enrolled at each school minus Days Suspended minus Absences

Totals Takes the first day that a student is enrolled (counts the days that the school is in session and the student is not marked absent) Student must be enrolled during the time frame for that month.

Total Days Absent - Total days absent minus suspensions

Examples
Student enrolls in school 11111 on 7/15/2011
Student withdraws from School 11111 on 11/15/2011
Student enrolls in school 22222 on 11/20/2011

Total Days Present should be totaled for each student for each month that they are in a school.

I need 1 row of data for the above student for each month that contains total days suspended, total days present and total days absent.
Pupil Number School Month Totals Days Suspended Total Days Absent Total Days Present

1234 11111 07 0 0 0
1234 11111 08 0 0 4 (school days 4)
1234 11111 09 3 1 16 (schools days 20 - 3-1)
1234 11111 10 0 1 18 (school days 19 - 1)
1234 11111 11 1 1 7 (student enrolled at school A 9 days -1-1)
1234 22222 11 0 1 10 (student enrolled at school B 11 days -1)
1234 22222 12 1 2 17 (school B 20-1-2)
1234 22222 01 0 0 20

View 2 Replies View Related

SQL & PL/SQL :: Get Total Number Of Rows Returned By Query?

Sep 17, 2010

DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;

[Code].....

How can I get the total number of rows returned by the query?

I want to be able to check omething like c1.ROWS = 0

View 4 Replies View Related

SQL & PL/SQL :: Query To Add Total Number Of Count Of Each Table

Jun 13, 2011

I want to know that whether we can write a query to add total number of count of each table.

for example:

select count(*) from emp;

count(*)
14

select count(*) from emp_1;

count(*)
15

select count(*) from emp_2;

count(*)
16

My question is that is it possible to add this all the three results and get the value as 45.

View 3 Replies View Related

PL/SQL :: Create Query To Display Total No Of Employees?

Aug 22, 2012

How can I create a query to display the total no of employees and, of that total, the number of employees hired in 1995,1996,1997,1998.

I am using Oracle® Database Express Edition 10g Release 2 (10.2)

View 3 Replies View Related

SQL & PL/SQL :: Hierarchy Query - Calculate Total Miles

Jul 30, 2013

I've below tables.

create table tst_link (parent_pid number(5),
child_pid number(5)
);

insert into tst_link (1, 2);

[Code]....

For each parent pid i need to calculate total miles (including the child_pid also).

Required Output

PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125

View 4 Replies View Related

PL/SQL :: Two Decimal Points

Aug 10, 2012

What would be the syntax if I want to use use '999G999G990D00' to show only two decimal points for outstanding_receipt_value in the following

((declared_quantity - delivered_qty) * po_unit_price) outstanding_receipt_value

View 3 Replies View Related

PL/SQL :: Generate All Dates Between To Date Points

Sep 8, 2012

Is there a way I can generate a list of date using a simple SELECT statement to generate all dates between to date points? For example between SYSDATE AND SYSDATE-7?

One way I know is have a table and run PL/SQL script to put all dates in it and query that but was wondering if it can be achieve via SQL SELECT query at all without creating a table (querying DUAL).

View 5 Replies View Related

Spatial :: Splitting Line At Multiple Points

Apr 22, 2013

I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).

However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.

View 13 Replies View Related

Server Administration :: Why 3 Mount Points For Datafiles

Feb 3, 2013

i encoutered lately on such statement:

Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....

why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?

View 4 Replies View Related

Spatial :: Decimal Points - Geometry - Sql Developer Geographer?

Aug 13, 2013

I have loaded shape files with SQL developer Georaper and limited number of decimals to 8. There is option to limit number of decimals in georapter.But there are some X, Y with many decimals. This may create problem in spatial functions in which geometries are compared and filtered

how to control the decimal points of X,Y to be loaded in geometry column.  -0.4695623500000000172249770002963487058878                    51.47160360000000167701728059910237789154-0.4702653600000000211345252409955719485879                    51.47174944999999723904693382792174816132-0.4713475400000000092859409051015973091125                    51.47330664999999783049133839085698127747-0.4717886499999999760923685698799090459943  
                 
[code]...

View 2 Replies View Related

Spatial :: Sdo_nn To Fetch Points From Point Cloud And Georaster

Mar 9, 2013

I have a requirement to fetch the nearest neighbor from a given point coordinates inside a point cloud/georaster data. how can i retrieve a collection of all neighboring points from a given point.

I guess i will have to use sdo_nn operator. how to use the operator to achieve the result.

View 21 Replies View Related

Application Express :: How To Manage Positions Of Different Display Points Of Theme

Apr 6, 2013

I'm developing, or trying, on Apex 4.2...The issue is about themes and how to manage the positions of diferents Display Points of a theme.

Everybody know that any theme have a map where its position all differents parts of this map (Body1, Body2, Body3, position01, position02 and so). So when you choice any theme, it come with all this Display points defined. You can see this map rendering any region page and in the item Display Point you can see this map clicking on the light.

There is anyway to can manage this map and change position or behavior of those points?. For example , I'm interested that under the tabs, to have at the same height, 3 diferentes position, one at left, one at center and other at right.

View 2 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

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 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 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 :: 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 :: 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







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