PL/SQL :: Select All With Count And Sum

Jan 8, 2013

i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.

For example:

select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Count Using 2 Tables?

Mar 28, 2011

I'm having trouble with some SQL code regarding count and an outer join.

Here is my code.

SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;

Where salesperson is a salesperson table and ord is a table containing orders.

The orders table contains a FK to salespersonid in the salesperson table.

I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.

View 13 Replies View Related

PL/SQL :: SUM Of SELECT Originator COUNT(*) As Inductions

Aug 29, 2012

I'm trying to SUM the results of "*SELECT originator, COUNT(*) as Inductions*" but am having trouble finding a solution? Can this be done and cause it to create the SUM in a row below the created Inductions column?

View 6 Replies View Related

Record Count Mismatch Between Select And Insert

Mar 20, 2013

We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 3 Replies View Related

SQL & PL/SQL :: SELECT With Count Of Rows Depending On Sum Of A Field?

Aug 10, 2011

I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.

At first a code sample of the complete selection:

SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%')
AND saext='M'
ORDER BY qty DESC,ktxt;

This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.

So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.

1. Can I do this in a query?

2. If yes, what would this query look like?

View 19 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

SQL & PL/SQL :: Setting Select Count Value Into Output Variable

Apr 12, 2011

I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.

create or replace
PROCEDURE BOOK_PASSENGER(
EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
NUMBER_BOOKED OUT NUMBER)
AS
BEGIN
INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);
SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;
END BOOK_PASSENGER;

View 6 Replies View Related

SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query

Oct 21, 2012

I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)

2. The number of assignments created from it that have been approved.

3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

[code]....

Assignment :

id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY

[code]....

Research_paper:

id name
----------
1A
2B

Expected result :

r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3

I wrote the following query for that :

SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'

[code]....

But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.

View 7 Replies View Related

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

Nov 16, 2009

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?

View 13 Replies View Related

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

Nov 24, 2011

difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.

SQL> select * from t1;

A B C
---------- -------------------- --------------------
1 2 3
2
5

SQL> select rownum,a.* from t1 a;

ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

Mar 3, 2011

I have a table with around 80 columns. All i need is to select first 40 columns.

Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.

View 2 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

Application Express :: How To Select MIN Value Under Default Tag Of Select List

Oct 5, 2012

I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."

BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.

BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.

THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.

View 7 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

PL/SQL :: How To Count

Feb 28, 2013

I have one tbale like

TABLE A with columns bu_code and bu_cty

bu_code bu_cty
12 ES
12 ES
13 AP
13 AP

so now i want to know the count for bu_code

like my output shd be

bu_code(12)    ---count is 2
bu_code(13)------count is 13

View 4 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

Count For A Particular String?

Oct 9, 2008

say there is astring "mumbojumbo "i need the count of given string in it

ex:when o is given count shuld be 2 when m is given count shuld be 3

is there any pre defined function for counting a given string ...

View 2 Replies View Related

SQL & PL/SQL :: Count Age In A Range

Mar 26, 2012

I have a table with the following fields :

RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)

[Code]..

I need a report as hereunder :

Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15

I have work out a script but the age is not in a range

select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area

View 1 Replies View Related

SQL & PL/SQL :: Count In Columns?

Feb 14, 2012

I have the following table with insert below.

CREATE TABLE ATTENDANCE
(
"REG_NO" NUMBER(7,0),
"COURSE_SEQ_NO" NUMBER(4,0),
"SECTION" VARCHAR2(1 BYTE),
"SEMESTER_CODE" NUMBER(1,0),

[code]...

How can I have the following output?

course_seq_nosectiontotal_recordscount_1count_0
322X20137

I tried decode but it could not find its way

View 9 Replies View Related

SQL & PL/SQL :: Count And Sort?

Mar 10, 2010

I am running this query but am not getting data that is correct.

SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc

View 8 Replies View Related

SQL & PL/SQL :: Group By For Getting Count

Mar 7, 2013

I need suing group by for getting count.I have a table with columns below

SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT

178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461

I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT

I want to get
1789 3 4

I tried this query but this is not working

[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from

my_table sub
group by sys_audit_id [/code]

View 2 Replies View Related

SQL & PL/SQL :: Get Accumulated Value For A Count

Dec 13, 2012

I need to get an accumulated value for a count. E.g. The table has purchased date, purchased item, purchased item type. The count of purchased item groyup by purchased type on every purchased date. Now, we got the count value (purchased item). But, I want the accumulated count value on every purchased date. So that I will get that how many items has been purchased on a particular date.

View 5 Replies View Related

SQL & PL/SQL :: Joins With COUNT

Nov 16, 2011

I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.

My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:

SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12

-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL

Here is the query in it's current state:

-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)

[Code]....

So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.

I'm trying the various joins, but none of them seem to be working.

I've tried the old 8i (+) join as follows:

AND c.fol_id = d.doc_fol_id(+)

I've tried the inner join:

AND c.fol_id(+) = d.doc_fol_id

...and I've tried the 9i method (left outer and full outer) using the following types of notations:

folder c full outer join documend d on c.fol_id = d.doc_fol_id

...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)

View 9 Replies View Related

SQL Inline Count

Jun 14, 2007

we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.

Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?

View 1 Replies View Related

PL/SQL :: How To Do Count In Table

Sep 24, 2012

I have a table employee

name title

A manager
B sales
c manager
D engineer
E marketing

I want to know how many people has title as manager?

the answer is 2 how to do the count, I can't do count(title), right ?

View 2 Replies View Related

SQL & PL/SQL :: Count No Null

Jul 13, 2011

SQL> select * from test11;

A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA

How to count no null in the above table

View 9 Replies View Related

PL/SQL :: Count Columns In A Row

May 3, 2013

oracle SQL,

Student(unique) has five races in a table

I need a select statement with student_id - 1 column

I need to count the number columns with values 'Yes' in them

the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn

The 3rd column should contain the max of the race having 'yes' in them

i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column

View 5 Replies View Related

SQL & PL/SQL :: Count From 1 Table By 2

Apr 23, 2012

I want the number of UNITS with the Status EN/PL for AREA 3/6

ID 1 has area 3 and 6 but only EN so count 1 (no PL)
ID 2 has area 6 but DR so no count
ID 3 has area 3 and 6 for EN and PL so 1 for each
ID 4 has area 6 three times and 3 once so count.

CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)

[Code]...

WANT:

IDENROLLEDPLANNED
11 0
20 0
31 1
41 3

View 2 Replies View Related







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