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


ADVERTISEMENT

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

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

SQL & PL/SQL :: Single Row Query Returns More Than One Row?

Oct 3, 2010

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

View 16 Replies View Related

PL/SQL :: Single Row Query Returns More Than One Row?

Mar 12, 2013

this is my sql=
" SELECT 

hpn.id AS id,
hpn.psn_id,
hpn.last_name,

[Code].....

"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,

but it returns error single row query returns more than one row, because one people can have more that one card_number.

View 9 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related

PL/SQL :: Single Sub Row Query Returns More Than 1 Row?

Apr 19, 2013

I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.

I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'

Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:

PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..

So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?

I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?

For reference, here is the query.

update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')

View 5 Replies View Related

PL/SQL :: Select Top Row In Single Query?

Sep 19, 2013

write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.

View 6 Replies View Related

All Employee Types In Single Query

Jul 7, 2011

I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.

I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..

like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')

View 3 Replies View Related

SQL & PL/SQL :: Convert Procedure To Single Query

Jul 26, 2012

I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.

FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER)
RETURN VARCHAR2 IS
v_act_shows booking_wk_screen.act_shows%TYPE;
v_expected_shows booking_wk_screen.expected_shows%TYPE;
v_return VARCHAR2(50);
BEGIN
SELECT NVL(act_shows, 1), NVL(expected_shows, 1)
INTO v_act_shows, v_expected_shows
FROM booking_wk_screen
WHERE booking_wk_id = i_booking_wk_id
[code]...

Is there anyway through which we can achieve this in Oracle 10g.

View 16 Replies View Related

SQL & PL/SQL :: Single Return Sub Query Returning More Value

Aug 21, 2013

i am trying to update below statement that has multiple rows but giving the error like :

update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))

error:- single return sub query return more value.

how to write update join query base on multi[ple ow.

View 8 Replies View Related

SQL & PL/SQL :: Drop All Views With Single Query

Jun 8, 2010

In scott/tiger user total 30 view tables are stored.Right now i want to drop all views with using query.

View 3 Replies View Related

SQL & PL/SQL :: Select Single Query And GroupBy Together?

Jul 8, 2011

i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.

select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description

[code]...

i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)

the second query was like:

SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Query - Get Details Of Each Employee In Single Row?

Jun 7, 2012

I have three tables as shown in the image. Need to get the details of each employee in a single row..

EMployee_id BaseSalary Bonus Hike shares

View 4 Replies View Related

SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query

Nov 22, 2011

How to update two tables in single set or single query ?

View 8 Replies View Related

SQL & PL/SQL :: Multiple Selects In Single Query

Apr 10, 2012

how does this query execute? what kind of a query is this called?

mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;

+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

View 8 Replies View Related

SQL & PL/SQL :: Incorporate Two Counts In A Single Query?

Sep 26, 2012

User table

id | name
----------
2 | Harry
3 | Mary

Course_User table

summer_course_completed | winter_course_completed | user_id | attendance
------------------------------------------------------------------------
y | n | 2 | 20
y | n | 2 | 40
y | y | 2 | 30
n | n | 3 | 20
n | y | 3 | 60

I wish to list each student's name with the number of summer courses he has completed and the number of winter courses he has completed. I am trying this :

select u.name, count(*)
from user u, course_user cu
where u.id=cu.user_id and cu.summer_course_completed = 'y'
group by u.id;

but I can get only the number of summer courses OR the number of winter courses that each student has completed, but never both simultaneously, through a single query. Is there a way to do that ?

View 7 Replies View Related

PL/SQL :: Single Query Return Multiple Value

Sep 6, 2012

I have a sql query as below :

select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............

Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.

In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values

View 3 Replies View Related

SQL & PL/SQL :: Show Same Record Twice With A Single Query?

Sep 24, 2012

I have a scenario for which I need to show same record twice.

i.e

SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul

Is this possible with a single query.

View 7 Replies View Related

SQL & PL/SQL :: Single Quote(') In Query Not Working?

Jan 5, 2013

I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).

The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;

returns nothing.

Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;

returns character '?'.

View 4 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.

I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.

View 10 Replies View Related

SQL & PL/SQL :: ORA 01427 - Single Row Sub Query Returns More Than One Record?

Oct 10, 2013

I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.

update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";

I understand that problem is there with inner select query.

View 3 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

SQL & PL/SQL :: Cross Referencing Query From A Single Table

Mar 21, 2012

I have a employee table with the below format

EMP

EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D

Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.

I need the output as below (many to many I.e. cross referencing)

EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1

View 1 Replies View Related







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