OraFAQ Forum: SQL & PL/SQL » Count Records Help

Home » SQL & PL/SQL » SQL & PL/SQL » Count Records Help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Count Records Help [message #9495]

Sun, 16 November 2003 01:12

Sam
Messages: 255
Registered: April 2000

Senior Member

Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

Report message to a moderator

Re: Count Records Help [message #9501 is a reply to message #9495]

Mon, 17 November 2003 01:04

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Assume you have a table t like this

SQL> Select *
  2    From t;

HTEAM       OTEAM       MATCHDATE    HPOINTS    OPOINTS
----------- ----------- --------- ---------- ----------
 CARINE      CHANTAL    24-NOV-03          5          2
 CARINE      CHRISTELLE 01-DEC-03          6          7
 CARINE      DANIEL     08-DEC-03          6          7
 CARINE      DAVID      15-DEC-03          0          7
 CARINE      DOMINIQUE  22-DEC-03          4          6
 CARINE      GERALDINE  29-DEC-03          4          1
 CARINE      JACQUES    05-JAN-04          6          3
 CHANTAL     CARINE     24-NOV-03          2          2
 CHANTAL     CHRISTELLE 24-NOV-03          1          7
 CHANTAL     DANIEL     01-DEC-03          5          4
 CHANTAL     DAVID      08-DEC-03          1          3
 CHANTAL     DOMINIQUE  15-DEC-03          1          1
 CHANTAL     GERALDINE  22-DEC-03          1          3
 CHANTAL     JACQUES    29-DEC-03          1          5
 CHRISTELLE  CARINE     01-DEC-03          0          6
 CHRISTELLE  CHANTAL    24-NOV-03          5          4
 CHRISTELLE  DANIEL     24-NOV-03          1          1
 CHRISTELLE  DAVID      01-DEC-03          6          3
 CHRISTELLE  DOMINIQUE  08-DEC-03          7          3
 CHRISTELLE  GERALDINE  15-DEC-03          7          6
 CHRISTELLE  JACQUES    22-DEC-03          6          6
 DANIEL      CARINE     08-DEC-03          5          3
 DANIEL      CHANTAL    01-DEC-03          5          1
 DANIEL      CHRISTELLE 24-NOV-03          3          6
 DANIEL      DAVID      24-NOV-03          5          5
 DANIEL      DOMINIQUE  01-DEC-03          3          0
 DANIEL      GERALDINE  08-DEC-03          7          7
 DANIEL      JACQUES    15-DEC-03          5          7
 DAVID       CARINE     15-DEC-03          5          0
 DAVID       CHANTAL    08-DEC-03          5          6
 DAVID       CHRISTELLE 01-DEC-03          6          5
 DAVID       DANIEL     24-NOV-03          4          5
 DAVID       DOMINIQUE  24-NOV-03          7          3
 DAVID       GERALDINE  01-DEC-03          4          6
 DAVID       JACQUES    08-DEC-03          6          2
 DOMINIQUE   CARINE     22-DEC-03          2          0
 DOMINIQUE   CHANTAL    15-DEC-03          6          3
 DOMINIQUE   CHRISTELLE 08-DEC-03          6          6
 DOMINIQUE   DANIEL     01-DEC-03          4          3
 DOMINIQUE   DAVID      24-NOV-03          0          6
 DOMINIQUE   GERALDINE  24-NOV-03          1          4
 DOMINIQUE   JACQUES    01-DEC-03          5          7
 GERALDINE   CARINE     29-DEC-03          6          3
 GERALDINE   CHANTAL    22-DEC-03          4          7
 GERALDINE   CHRISTELLE 15-DEC-03          2          4
 GERALDINE   DANIEL     08-DEC-03          2          6
 GERALDINE   DAVID      01-DEC-03          1          4
 GERALDINE   DOMINIQUE  24-NOV-03          5          6
 GERALDINE   JACQUES    24-NOV-03          2          2
 JACQUES     CARINE     05-JAN-04          1          3
 JACQUES     CHANTAL    29-DEC-03          3          5
 JACQUES     CHRISTELLE 22-DEC-03          6          4
 JACQUES     DANIEL     15-DEC-03          1          2
 JACQUES     DAVID      08-DEC-03          6          4
 JACQUES     DOMINIQUE  01-DEC-03          3          5
 JACQUES     GERALDINE  24-NOV-03          6          6

56 rows selected.

Table t contains all matches between the 8 players. You want to display the total home scores (hpoints), matches the home player (hteam) won and what the percentage won is. This is what I would do:

SQL> Select sum(hpoints) total_home
  2       , hteam
  3       , Sum(decode(sign(hpoints-opoints),1,1,0)) nr_wins
  4       , count(hpoints) nr_games
  5       , round((Sum(decode(sign(hpoints-opoints),1,1,0))/count(hpoints))*100,2) percentage
  6    From t
  7   group by hteam
  8   Order By total_home Desc
  9  /

TOTAL_HOME HTEAM          NR_WINS   NR_GAMES PERCENTAGE
---------- ----------- ---------- ---------- ----------
        37  DAVID               4          7      57,14
        33  DANIEL              3          7      42,86
        32  CHRISTELLE          4          7      57,14
        31  CARINE              3          7      42,86
        26  JACQUES             2          7      28,57
        24  DOMINIQUE           3          7      42,86
        22  GERALDINE           1          7      14,29
        12  CHANTAL             1          7      14,29

8 rows selected.

HTH,
MHE

Report message to a moderator

Re: Count Records Help [message #9502 is a reply to message #9501]

Mon, 17 November 2003 02:32

Sam
Messages: 255
Registered: April 2000

Senior Member

OK. I see how you did this but having trouble setting it up. Here are the tables I have. I have some null values as I am only includin the info neededed in the reports.

CREATE TABLE SCHOOL
(school VARCHAR2(30),
conference VARCHAR2(25),
stadium_size NUMBER(6),
ticket_price NUMBER(4,2),
in_state_players NUMBER(2),
outstate_players NUMBER(2),
scholarships NUMBER(2),
graduate NUMBER(20));

CREATE TABLE SCHOOL_INCIDENTS
(school VARCHAR2(30),
idate DATE,
incident_code NUMBER(5));

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
------------------------- -------------- --------------------
Big Ten 46000 17983
Big Ten 45000 17983
Big Ten 44000 17983
Big Ten 43000 17983
Big Ten 42000 17983
Big Ten 41000 17983
Big Ten 40000 17983
Big Ten 39000 17983
Big Ten 38000 17983
Big Ten 37000 17983
Big Ten 36000 17983

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
------------------------- -------------- --------------------
Independent 51000 17250
Independent 50000 17250
Independent 49000 17250
Independent 48000 17250
Independent 47000 17250
Independent 46000 17250
Independent 45000 17250
Independent 44000 17250
Independent 43000 17250
Independent 42000 17250
Independent 41000 17250

Report message to a moderator

Re: Count Records Help [message #9503 is a reply to message #9502]

Mon, 17 November 2003 03:49

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You need to GROUP BY CONFERENCE
See how this works for you:

SELECT S.CONFERENCE
<B>     , SUM(DECODE(SIGN(H.SELF_SCORE-H.OPP_SCORE)
                 ,1 -- self_score > opp_score 
                 ,1 -- return 1
                 ,0 -- else return 0
                 ) -- make the sum
           )/COUNT(H.SELF_SCORE) -- divide by the number of occurrences per conference
           "WINNING PERCENTAGE"</B>
     , <B>AVG(H.ATTENDANCE)</B> "AVG ATTENDANCE"
--     , I.INCIDENT_CODE "RECRUITING INCIDENTS" 
-- For this last column 'i.incident_code':
-- either perform a group operation like AVG or 
-- include in the group by otherwise you'll get an 
-- error.
  FROM SCHOOL S
     , SCHOOL_INCIDENTS I
     , HOMEGAME H
 WHERE S.SCHOOL = H.SCHOOL
   AND S.SCHOOL = I.SCHOOL
 <B>GROUP BY S.CONFERENCE</B>;

I tried to make it as clear as possible, but what you want is to group the output per conference. Once you did that, you can perform group operations on it.

MHE

Report message to a moderator

Re: Count Records Help [message #9506 is a reply to message #9503]

Mon, 17 November 2003 08:47

Sam
Messages: 255
Registered: April 2000

Senior Member

I would need to count the occurences of incidents in the table this would not average as the input is only text. Any suggestions?

Report message to a moderator

Re: Count Records Help [message #9515 is a reply to message #9501]

Mon, 17 November 2003 23:13

Sam
Messages: 255
Registered: April 2000

Senior Member

Ok this works great except for the last column. I keep getting a count of 11 when there should only be a count of 2 for Big Ten (Indiana & Ohio) & 1 (Penn)for Independent. Here is the code: - Thanks for all your help and explanation.

SELECT S.CONFERENCE,
SUM(DECODE(SIGN(H.SELF_SCORE-H.OPP_SCORE),
1,1,0)) / COUNT(H.SELF_SCORE) "WINNING PERCENTAGE", AVG(H.ATTENDANCE) "AVG ATTENDANCE",
COUNT(I.INCIDENT_CODE) "RECRUITING INCIDENTS"
FROM SCHOOL S, SCHOOL_INCIDENTS I, HOMEGAME H WHERE S.SCHOOL = H.SCHOOL AND S.SCHOOL = I.SCHOOL
GROUP BY S.CONFERENCE;

CONFERENCE WINNING PERCENTAGE AVG ATTENDANCE RECRUITING INCIDENTS
------------------------- ------------------ -------------- --------------------
Big Ten .636363636 41000 11
Independent .727272727 46000 11

SQL>

Here are the records in the school_incident table there are null values for Idate:

SQL> SELECT * FROM school_incidents;

SCHOOL IDATE INCIDENT_CODE
------------------------------ --------- -------------
Indiana Univ. 17983
Ohio State Univ 12891
Penn State Univ. 17250

Report message to a moderator

Re: Count Records Help [message #9516 is a reply to message #9503]

Mon, 17 November 2003 23:18

Sam
Messages: 255
Registered: April 2000

Senior Member

Report message to a moderator

Re: Count Records Help [message #9518 is a reply to message #9516]

Tue, 18 November 2003 01:33

Maaher
Messages: 7065
Registered: December 2001

Senior Member

First a remark: The percentage needs to be multiplied by 100 to get an actual percentage....

So, you want to count the different incident codes: Use this instead:

SELECT S.CONFERENCE
     , SUM(DECODE( SIGN(H.SELF_SCORE-H.OPP_SCORE)
                 ,1 , 1
                 ,0
          ) / COUNT(H.SELF_SCORE) "WINNING PERCENTAGE"
     , AVG(H.ATTENDANCE) "AVG ATTENDANCE"
     , COUNT(<FONT COLOR='RED'><B>DISTINCT(</B></FONT>I.INCIDENT_CODE<FONT COLOR='RED'><B>)</B></FONT>) "RECRUITING INCIDENTS"
  FROM SCHOOL S
     , SCHOOL_INCIDENTS I
     , HOMEGAME H 
 WHERE S.SCHOOL = H.SCHOOL 
   AND S.SCHOOL = I.SCHOOL
 GROUP BY S.CONFERENCE;

The distinct keyword only counts the distinctive values. As you will notice, with the data provided by you the count will probably be 1 in both records, this is normal considering the fact that "Ohio State Univ" with incident code "12891" is not present in the sample data you provided in one of your earlier replies.

MHE

Report message to a moderator