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
ADVERTISEMENT
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
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
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
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
Feb 16, 2012
multiple occurrences in table. I have attached a sample table in csv format along with my code.
I'm trying to get all the information from the table where the section number occurs more than one time.
In my table SECTION 103 and 104 only occurs one time. I'm trying to eliminate 103 and 104. I only want to see sections that occur more than once.
Below is my
select a.term_code_key term, a.crn_key, a.seq_number_key section,
a.subj_code||'-'||a.crse_number subject, a.title,
a.actual_enrollment seats_filled, maximum_enrollment seat_cap,
a.seats_available empty_seats
[Code]....
View 6 Replies
View Related
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
Jan 9, 2007
Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
View 4 Replies
View Related
Feb 24, 2010
For every record which came yeterday in table ABC, I need to find if same record data does exists in historical records of the same table, and if match is find, I need to display all the occurrences.
For example Data got inserted yesterday as
SQL RES : Col 1= "A" Col 2: "B" Col 3: "C" Date = Sysdate -1
then, I need to scan through Query results from the history where I can find if there exists same combination already.and if so I need to display both i.e Record form yesterday and from history.
Here is the one I am using to get the base data.
with base as(
select Col 1, Col 2, Col 3,A.Create_DT
from A,B
where A.ID = B.ID
)
View 20 Replies
View Related
Jul 11, 2012
How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
View 4 Replies
View Related
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
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
Nov 8, 2013
I have a table of documents (excel, word, pdf, txt) stored in blob column. I want that SCORE returns the number of occurrences within each document, so I'm using this piece of code:
SELECT bdt.*, SCORE(1) AS score_1, DBMS_CRYPTO.HASH(archivo, 3) AS cod_hashFROM crg_ctx_archivos_recibidos bdt
WHERE CONTAINS(archivo,'<query> <textquery lang="SPANISH" grammar="CONTEXT">DEFINESCORE(paz, OCCURRENCE * 0.1)</textquery> <score datatype="INTEGER" algorithm="COUNT"/></query>', 1) > 0
ORDER BY SCORE (1) DESC;
However I'm getting a maximum score of 10, but I know one of these documents have 490 matches. How can i get the exact number of matches per document? I'm using Oracle 11.2
View 3 Replies
View Related
Aug 14, 2012
My Scenario is'....456re0,50kg400,500rfabs43qre30,25kg150,354rf658....'
there is possible,using regexp_substr or other way to get the values, 0,50 and 400,500 and 30,25 and 150,354? I'm using [^re]+[$kg] and the string comes, but only the first occurence..
View 5 Replies
View Related
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
Apr 20, 2013
I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4
The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3 -> First occurrence in the total of 3
1-2 TRALTEST43S1 2 3 -> Second occurrence in the total of 3
3 TRALTEST43S1 3 3 -> Third occurrence in the total of 3
4-6 TRALTEST43S2 1 4 -> First occurrence in the total of 4
4-6 TRALTEST43S2 2 4 -> Second occurrence in the total of 4
4-6 TRALTEST43S2 3 4 -> Third occurrence in the total of 4
7 TRALTEST43S12 4 4 -> Fourth occurrence in the total of 4
This is the query that I have so far:
SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
SELECT
WIPDATAVALUE
, SLOT
, (dulo - una) + 1 AS m
[code]....
I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.
View 2 Replies
View Related
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
Jul 24, 2011
I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.
In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.
CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')
[code].....
View 3 Replies
View Related
Feb 4, 2012
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
View 13 Replies
View Related
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
Apr 20, 2013
here on your forum but has been following several threads ever since.So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. Something like this:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4-----
As you can see above, on the SLOT TRALTEST43S1, there are three occurrences so M (Total number of occurrences) should be three and that column N should count it. Same goes with the SLOT TRALTEST43S2. This is the query that I have so far:
SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
[code]...
I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.
View 23 Replies
View Related
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
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
