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.
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
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!
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.
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
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;
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]....
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.
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
