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
ADVERTISEMENT
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
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
View Related
Feb 2, 2012
Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.
Example as needed:
NoPendingDays = 23 (8+15)
I have attached Create table and Insert table values sample as SQL file.
View 10 Replies
View Related
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
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
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
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
May 16, 2007
I have a form that contains some data and I would like to count them in a new "Item text" ... I don't want to use the Item Text's property where we set the data type to number, database to No, Query all record to Yes and etc... I want to use the SQL statement code to count the records (using trigger)... I named the Item Text as Count_Records .
View 1 Replies
View Related
Jul 13, 2010
I am working with an asp.net/vb.net application whose users have oracle accounts to connect to the application.The database is set up to allow three failed login attempts. When connecting directly to the database, the sys.user$ lcount field increments on unsuccessful login attempts, and resets to 0 on successful login attepts. This is what I expect should happen.
However, when logging in through the application, using:
objConnection = New OracleConnection(m_strConnectionString)
objConnection.Open()
the lcount field sporatically increments on unsuccessful login attempts (sometimes it does, sometimes it doesn't), and never resets to 0 on successful login attempts. I would have expected it to behave as it does when connecting directly to the database.
Why does the lcount field not reset on a successful login through the application? How can I make it increment on each unsuccessful login attempt and reset on each successful login attempt?
View 1 Replies
View Related
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
Apr 1, 2010
I need to list a count of rows where a DATE field is not null and group those counts by day.
Here's my sql so far...
SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;
Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?
View 2 Replies
View Related
Sep 24, 2012
I am trying to find some way how to tune and optimize the server performance in following situation. There are 100s of sessions inserting records to one table. Sessions are communication threads in java application, each thread is receiving messages that are to be stored in the table. Each message must be commited and then is ACK sent to remove client. Two problems are raising of course - much of ITLs on the table and lots of very small transactions. I can adjust the java application, but cant do much about the design.
I was thinking about some "caching" - if the messages are stored in memory and bulk-inserted to database by single thread the performance would be much higher. However, there would be possible loss of data - the message could be lost from memory cache and client already received ACK.
View 10 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Oct 9, 2007
there are some data in the table que_history (seqnbr is the key), e.g.
SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39
I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .
select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;
but the result is not the right.
View 3 Replies
View Related
Dec 5, 2012
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
View 5 Replies
View Related
Nov 14, 2011
I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
View 5 Replies
View Related
Oct 18, 2010
I have a sql like this
SELECT RO_NO, HANDLED_BY
FROM MSS_VEH_INPUT
GROUP BY HANDLED_BY, RO_NO
I want the output like this with row num grouped by handled_by,ro_no
SNO HANDLED_BY RO_NO
1 KUMAR 12
2 KUMAR 13
3 KUMAR 14
1 LEE 15
2 LEE 16
1 MARVIN 17
2 MARVIN 18
So from this I can know Kumar how many ro,lee how many ro and marvin ro.?
View 6 Replies
View Related
Mar 17, 2010
i have a doubt in building a query.
I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
View 12 Replies
View Related
