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


ADVERTISEMENT

One Schema For Several People?

Jan 28, 2008

In our Oracle 9i Database we have a Project that uses one Schema called ProjectOne where several people (working together) need to be able to create tables, create,edit and delete records, create and delete triggers and sequences in the ProjectOne schema.

Our DBA creates a user account for each person so they have a different username and password with priviledges to the ProjectOne Schema. The DBA doesnt want us to use the ProjectOne Schema username and password. Is this a standard practice with Oracle where each person needs their own username and password to login into the Database and view and edit one Schema (ProjectOne)?

View 1 Replies View Related

Performance Tuning :: SQl ID From People Soft Process

Jan 18, 2012

Our developer team has given two process instance number[the process is running from Peoplesoft] that are running beyond the time limit.I need to know the SQL ID from the process instance number. Is there is any way to know the SQL ID?

View 5 Replies View Related

Find Total Of All Active People For Each Region Code

Jun 18, 2012

I would like to run a query that counts by case_manager, number of distinct app_id's that have a status ='AC' in a select number of programs. All of the fields are in the same table.I want it to look like this:

Case MgrA DYYOY
Jane13420
John3452
Alice1233

Fields are case_manager, status, applicant, and program table reg...I can do the count command to find the total of all active people for each region code. What I want it for the breakdown by program the people are in.

My query for that is:
SELECT case_manager, Count (*)
from reg
where status='AC'
Group by case_manager
order by case_manager
and I get this:
Case ManagerCount *
Jane46
John 14
Alice9

View 1 Replies View Related

Server Utilities :: Wrap PL/SQL Codes And Other People Will Not Be Able To Unwrap It?

Feb 27, 2013

Is there a way we can wrap pl sql codes and other people will not be able to unwrap it? because I came across this site successfully unwrapped my wrapped pl sql codes. How to just unwrapped pl sql code then what is the use of wrapping?

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

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

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

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

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

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







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