SQL & PL/SQL :: De-normalizing A Log Table And Counting Distinct Rows?

Oct 6, 2011

I have a table in which I can see via which communication channel we have previously communicated with our customers on different dates.

customer_interaction_log (I removed the date column)

customer_id channel
------------------------
1 SMS
1SMS
1SMS
1Email
1Email
2SMS

My goal is to have a de-normalized summary table in which total communication volume for each distinct channel is displayed per customer.Briefly, I would like to reach the following output

customer_interaction_summary

customer_id sms_countemail_count
-----------------------------------
132
210 (or null)

When I run the sql below I am not able to de-normalize by customer id; counts are accurate.

SELECT distinct cil.customer_id,
(CASE channel WHEN 'SMS'
THEN (SELECT COUNT (channel) FROM customer_interaction_log cil1
where cil1.channel='SMS' and cil1.customer_id=cil.customer_id) END) SMS_COUNT,
(CASE channel WHEN 'Email'
THEN (SELECT COUNT (channel) FROM customer_interaction_log cil2
where cil2.channel='Email' and cil2.customer_id=cil.customer_id) END) EMAIL_COUNT
FROM customer_interaction_log cil;

Output:

customer_id sms_countemail_count
-----------------------------------
1 3
21
1 2

I am trying to have one row per customer, however as you can see above it is not the case.

CREATE TABLE CUSTOMER_INTERACTION_LOG (
CUSTOMER_ID NUMBER(2),
CHANNEL VARCHAR2(10 BYTE)
);
Insert into customer_interaction_log (CUSTOMER_ID, CHANNEL) Values (1, 'SMS');
Insert into customer_interaction_log (CUSTOMER_ID, CHANNEL) Values (1, 'SMS');

[code].....

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Counting Distinct Occurrences Of Data From Multiple Columns

Feb 4, 2013

CREATE TABLE FEB05
(NAME VARCHAR2(20),
CITY VARCHAR2(20))
INSERT INTO FEB05 VALUES ('AKON','CIT22')
INSERT INTO FEB05 VALUES ('MARIA','WOT101');
[code]......

I WANT A QUERY THAT WILL GIVE O/P AS TO GET DISTINCT COUNT OF NAME AND CITY COLUMN separately.

I.E. THE O/P SHOULD BE

Quote:Count(dist name) Count(dist city)
6 5

View 5 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 :: Counting Rows Of All The Tables In Database?

May 18, 2010

i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .

select count(*) from each table takes a long time , since there are 950 tables.

View 4 Replies View Related

Counting Rows - Difference In Values That Are Returned?

Sep 26, 2010

difference in the values that are returned?

select count(*) from aaa;
COUNT(*)
----------
1000001

select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202

View 5 Replies View Related

SQL & PL/SQL :: Query Returns Multiple Rows / How To Get Distinct Row

Jul 18, 2010

in 10g report builder i have written this query

----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP

[code]....

this query returns multiple row. how to get distinct row.

View 5 Replies View Related

SQL & PL/SQL :: How To Select Distinct Rows Using Join On 3 Views

May 12, 2011

I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:

select *
from myschema.view_1 acct
Left JOIN myschema.view_2 freq

[Code].....

View 8 Replies View Related

SQL & PL/SQL :: To Create Columns From Distinct Rows In Reporting

Feb 22, 2010

I have a table PRODUCT with following structure:

Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....

There may be any number of records in the table. Now I need to print distinct products in different columns such as.

AF LO SK
100 50 47

There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?

View 2 Replies View Related

SQL & PL/SQL :: Why Does SELECT DISTINCT Return Different Rows When Used With ORDER BY

Jan 4, 2013

nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.

CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);

[code]....

View 11 Replies View Related

Retrieve Distinct Values From Clob Using Distinct Operator

May 27, 2013

i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work

View 1 Replies View Related

SQL & PL/SQL :: Merge Two Table With Distinct Value

Sep 4, 2012

select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'

when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?

View 1 Replies View Related

SQL & PL/SQL :: How To Populate Distinct Column In A Table

Apr 22, 2013

I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?

View 3 Replies View Related

SQL & PL/SQL :: Distinct On Multiple Table Inner Join

Apr 28, 2010

I am little confused applying DISTINCT on a Multiple table Inner join.

Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com

Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4

Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4

Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4

QUERY::

select distinct c.process_id a.role_id,a.email_address,c.process_name
from role a, notification_role b, process c
where a.role_id=b.role_id and b.process_id = c.process_id

View 3 Replies View Related

PL/SQL :: Fetch DISTINCT Values From Partitioned Table

May 12, 2013

What is the fastest way to fetch DISTINCT values from partitioned table?

1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)

Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
  CNTRY_KEY     NUMBER,
  STUDY_ID      NUMBER,
  PRD_KEY_YEAR  NUMBER,
  PRD_KEY_WEEK  NUMBER,
  DATE_FROM     DATE,

[Code]...

-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)

** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR

[Code]...

** Explain Plan:
Plan
SELECT STATEMENT  ALL_ROWSCost: 6,235  Bytes: 629  Cardinality: 37                           
      8 HASH UNIQUE  Cost: 6,235  Bytes: 629  Cardinality: 37                      
         7 CONCATENATION                 
              3 PARTITION RANGE ITERATOR  Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 3  Partitions accessed #194 - #207          

[Code]...

Partition #: 7  Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.

View 12 Replies View Related

SQL & PL/SQL :: Counting Occurrences Of Max Value?

Feb 19, 2011

I have a table "exam results"

create table eresults
(student_name varchar2(20),
section_name varchar2(4),
exam_id NUMBER (4))
marks NUMBER (3))

[code]....

My requirement is that I need another column named "top scored" which will show how many times each student took highest marks for his Section in each exam_id"

For example in above data the following students "Top scored" for thier respective section in each exam_id:

STUDENT_NAME SECTION EXAM_ID
DOLLY A 1
RIZWAN B 1
PAUL C 2
ZAKIR D 2

[code]....

So, based on above my requirement is as below

STUDENT_N SECTION_NAME COUNT(EXAM_ID) SUM(MARKS) MAX(MARKS) top_scored
ALEENA C 2 147 91 0
ASIM D 2 68 45 1
ASLAM B 2 70 56 0
ATIF D 2 2 2 0
AYSHA B 2 114 78 0

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Counting Followed Last Occurrences

Jul 4, 2013

the following conundrum.

Having the Following information:

Col A Col B
---------------
134 | 1049
0 | 1050
12 | 1051
0 | 1052
0 | 1053
0 | 1054
0 | 1055
0 | 1056
0 | 1057

What I want is to count the number of the similar occurrences on Col A starting from the bottom and stopping at the first that is different.

Taking the example above I would get 6, that is the number of repeated "0" from the last value "1057" until "1052".

View 15 Replies View Related

Counting People With Same Birthdays

Jun 30, 2008

I have a q, If i have:

table1(p_id, birth_date,lid)
table2(lid)
table3(s_id, birth_date, lid)

Now i would like to count from table 1 all p_id with birthday is the same as a s_id.Currently i am doing this:

select s_id, count(tbl1.pid)
inner join table 3
using (lid)
inner join table 1
using (lid)
Where to_char(tbl1.birth_date,'DD') = to_char(tbl2.birth_date,'DD') AND
to_char(tbl1.birth_date,'MM') = to_char(tbl2.birth_date,'MM')

when i check it using data, i get a target_id whose birth_day is same as TWO people in tbl1 yet according to that query i am only getting ONE person! rest of the results from that query look fine!

View 6 Replies View Related

SQL & PL/SQL :: Counting Partners Who Appeared Together With Each Other?

Nov 27, 2011

I have a table t

create table T
(player varchar2(40),
for_team varchar2(10),
Vs_team varchar2(10),
matchid number,

[code]...

Note: Each player can appear for more than one team in different matches. For exmaple, John appeared for Team A in matchid = 1 and then in matchid = 2 he appeared for team B. So same could be for other players

Requirment: I want for each player, the sum of total number of matches and points (which is easy using SUM) but along with total number of different teammates he played with in all the matches he appeared [for his team(s)] and also the total number opposition players and lastly the total number of different players he played with in all the matches he appeared in.

Just to clarify some terms, incase any doubt:

Here teammates = all players who appeared (for_team) in a match from the same team for whom the respective player also appeared in the same match.

opposition players = all players who appeared for VS_team played by each player.

total different players = all unique players who appeared for for_team or vs_team in the matches in which each player appeared.

Here is my desired outout:
Player total Matches Sum(points) Different teammates Different opposition players total different players
player played with player played with player played with
John 3 4 5 5 10
Fulton 2 11 3 4 6
Sarah 1 9 2 3 5

[code]..

I want one simple query and shortest query to achieve about output since in my actual table data is huge.

View 4 Replies View Related

PL/SQL :: Counting Particular Word In String?

Jul 17, 2012

Have a query to find the occurence of a particular word in a string using a query.

ex: str := 'a,b,a,c,d'

search_str := 'a'

=> need to get the number of times 'a' is getting repeated in the string str.

output: 2

View 9 Replies View Related

SQL & PL/SQL :: Counting Records Within A Date Range?

Nov 1, 2011

I am trying to write part of an SQL where it gives me a count of bookings in any 6 month period made from the first booking.Example of records

enquirynumberenquiryaddresssubjectcodebookingdate
613651 Burberry AvenueBCHR20/10/2008 07:00:00
613801 Burberry AvenueBCHR20/11/2008 07:00:00693021 Barberry AvenueBCHR07/09/2009 07:00:00

I am so far getting 3 as a count result based on SQL below. I want the count to return 2 (because its inside the 6 month range):

SELECT
ce1.enquirynumber,
ce1.enquiryaddress,
es1.subjectcode,
b1.bookingdate,
(SELECT count(b2.bookingdate)
[code]....

View 2 Replies View Related

PL/SQL :: Counting The Number Of Lines For Each Procedure In A Package

Jul 31, 2012

I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?

for example in the following example i would like to count the lines between

"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;"
SQL> select text  from user_source where name='PKG_TEST' and type='PACKAGE BODY';

TEXT
--------------------------------------------------
PACKAGE BODY PKG_TEST IS
/*************************************************
****/
PROCEDURE proc1 IS
BEGIN
update t1 set EDITION_NAME = 'AAAAAAA';
commit;
END proc1;
[code]....

View 14 Replies View Related

Server Utilities :: Counting Position Of Data In Fixed Format?

Sep 5, 2010

How the people in production counts the exact position of the data in fixed format of Sql*loader? Isn't it critical especially in a critical and having many column to be inserted.

View 6 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

I am running a GROUP BY query on a few columns of enumerated data like:

select count(*), Condition, Size
group by Condition, Size;

COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S

Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.

For example:

COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL

View 9 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

Purging Table Which Is Having 98 M Rows

Dec 3, 2012

I want to purge a table which is having more then 98M rows...here are the details...

Purge Process I followed
---------------------------------------------
Step 1. Created backup table from Main table with required data only
create table abc_98M_purge as
select * from abc_98M where trunc(tran_date)>='01-jul-2011'
-> table created with 5325411 rows

Step 2. truncate table abc_98M

Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure

DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor cur_insert is select rowid from abc_98M_purge order by rowid;
BEGIN
open cur_insert;
loop
[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128

3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,

[code]....

4) Iam using the below proc to delete the rows from table, except the 4 rows returned above

declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;

[code]....

5) After running this procedure, i shud see 5124 records, but i see zero records.

View 23 Replies View Related







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