PL/SQL :: Get Count Of Particular Table Records

Mar 11, 2013

We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.

View 11 Replies


ADVERTISEMENT

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Table With Different Contents

Mar 31, 2010

I am having a table with contents like

item id, name
100,s_enter
100,p_enter
200,s_enter

i would like the output to be

Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)

I am working with basic case but thats not working.

select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table

View 3 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

Get Count Of Records Grouped By Another Field From Transactions Table

Nov 5, 2009

I have a table that cannot be changed with a field called transaction_reference in the transactions table. This field contains any number of some values in a look-up table called codes.

The table codes contains 'AA', 'BB', 'CC'.

A typical transaction_reference field may look like 'CC BB' or 'AA' or 'AA CC' or 'AA CC BB' - any number, any order.My goal is to get a count of records grouped by another field from the transactions table.

Transactions table example:
transaction_id | transaction_reference | family
---------------------------------------------
1 | AA BB | foo
2 | BB CC | bar
3 | BB | hello
4 | AA CC BB | foo
5 | BB AA | bar

So the results should look like:

family | code | count
foo | AA | 2
foo | BB | 2
foo | CC | 1
bar | AA | 1
bar | BB | 2
bar | CC | 1
hello | AA | 0
hello | BB | 1
hello | CC | 0

If the counts of 0 (like the third to last and last line above) don't show up I'm ok with that.I put together an explode function like this one here but I'm really not sure where to go from here. I can split the transaction_reference, but I'm not sure what to compare it to or how.

I realize that a field in the transactions table for AA, BB, and CC would be ideal, but I can't do that... the powers that be won't let me change the table.

for each exploded segment from transaction_reference
look for it in the codes table
if it exists, add 1 to the count

View 7 Replies View Related

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Count Of Records On Each Date?

Jan 26, 2011

PART_REF REGISTERED_BYREGISTERED_DATE
DCA544519 SVK 1/3/2011
DCA544520 SJA 1/3/2011
DCA544539 SJA 1/3/2011
DCA544572 THS 1/3/2011
DCA544608 GKA 1/3/2011
DCA544610 GKA 1/3/2011
DCA544611 THS1 1/3/2011

I just want a to get the total number of records registerd on particular date by particular user...

View 4 Replies View Related

SQL & PL/SQL :: Count Records Per Every 10 Minutes

Jun 6, 2011

How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.

The sample data has been enclosed with it.

View 13 Replies View Related

SQL & PL/SQL :: Get Count Of Records By Month

Aug 17, 2011

I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.

AUDIT_DATE SIGNAGE_ONE_LANE_ROAD_AHEAD SIGNAGE_LANE_CLOSED_AHEAD

1/5/2011 9:30 N/A N/A
2/9/2011 11:50 Yes Yes
2/21/2011 10:00 Yes Yes
2/22/2011 7:00 N/A N/A
3/7/2011 13:35 Yes N/A
3/8/2011 9:15 N/A N/A
3/9/2011 8:40 N/A N/A
3/10/2011 10:00 N/A N/A
[code]....

View 15 Replies View Related

SQL & PL/SQL :: How Many (count Of) Records Got Updated

Dec 8, 2011

I Have a table with 100records.after sometime i updated some records . But after that i want to know how many (count of) records got updated

View 3 Replies View Related

PL/SQL :: Get Records Count Of All Tables

Sep 26, 2012

I am trying to get the record count of all tables using dynamic query. I don't know how to put the value in placeholder. I tried the below code.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR table_list
IS
select OBJECT_NAME from user_objects
where object_type in ('TABLE')
[code].......          

View 4 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Item List

Apr 17, 2012

One of the tables is having

SQL> select count(1) from item_list;

COUNT(1)
----------
14356529

To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%

I tried finding the fragmentation using the below query.

select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc

TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306

View 15 Replies View Related

SQL & PL/SQL :: How To Get Distinct Count Of Records From CSV File

Mar 18, 2010

the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id

"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"

I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4

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

Getting Count Of Records / Returning Zero For Null Results?

Jul 23, 2009

I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.

This is the pre-existing query:

SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')

The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.

View 14 Replies View Related

SQL & PL/SQL :: Identify / Count Records In Ref Cursor Without Actually Fetching?

Jan 22, 2013

/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,

[code]....

I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.

Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.

one more solution is

OPEN
FETCH
CLOSE
OPEN (AGAIN) { this will lead to redundancy)

View 7 Replies View Related

Reports & Discoverer :: Count Records With Not Null Column

Jun 25, 2013

i want to count group above report records! want to count department group employees there in time is not null!

my report query

SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....

im doing it with formula column!

function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......

View 18 Replies View Related

PL/SQL :: Create View Which Is Aggregate Count Of Member Records / Grouped By Business_unit / Gender / Age Per Year

Oct 13, 2012

Using Oracle 11g...We have a table in our database of data with the following information:

MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]

The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:

business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573

The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

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

Find Out Row Count From Each Table?

May 6, 2009

I have written a script to get count of each tables of a schema.

DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;

[Code].....

View 1 Replies View Related

Find Out Row Count From Each Table

Mar 31, 2009

SQL> select count(*) from tab;
COUNT(*)
----------
63

I need to find out how many rows in each table.I've tried with cursor.

View 9 Replies View Related

SQL & PL/SQL :: Historical Table Count

Sep 9, 2012

One of my colleaque gives the below informations. it displays table count information while gathering stats. But I dont know from which view he picked the historical information. pls let me know the view name to get this detail.

NUM_ROWSANALYZETIMESAMPLESIZE
79736508-Aug-121594730
82169559-Aug-121643391
81993579-Aug-128199357
1035866010-Aug-122071732

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Count Distinct Rows In Table

Feb 21, 2013

I think this is easier than I think but all I want to do is count the distinct rows in a table.

WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL

[Code]..

seems to get what I want

ID1VAL1CNT1
111114
212224
321114
422224

but can the count be done in a stand alone query such as??

SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1

NOT what I want

ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253

View 5 Replies View Related

SQL & PL/SQL :: How To Count Rows Of Partition Table

Apr 9, 2013

I have tried to execute this query

select partition_name, num_rows from dba_tab_partitions where table_name='Partisi_Book_Issue';

it display 0 num_rows. But i have made non partitioned table to partitioned table with the same number of rows. how to display number of record in each partition in any table?

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

Application Express :: Show Count From Another Table In A Report

May 29, 2013

I am using Apex 3.2...I have the following sql in a report

select
a.jobid jobid,
a.jobid related_jobs,
to_char(a.startdate, 'dd.mm.yyyy hh24:mi:ss') startdate,
to_char(a.enddate, 'dd.mm.yyyy hh24:mi:ss') enddate

from jobs aI would like to show a count from another table where the jobid = jobid from the above sql.The problem is I don't know how to refer to the jobid in my extra sql.Here is my extra sql for the new count column.

(select count(1)
from jobs a, loghead b, dba_scheduler_job_run_details c
where a.logid = b.logid
and b.session_id(+) = c.session_id
and a.jobid = :P3_JOBID) no_of_related_jobs.

This will not work because :P3_JOBID is a page item.I have also tried #JOBID#, but got en error, as it was not recognised.

View 1 Replies View Related







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