SQL & PL/SQL

Hi,

Under APEX 24.2.2 and Oracle 19C, I want to create a bar graph which displays the total (sum) score of the players of a sport for a given year. There are two tables. The table PARTICIPANTS and the Table MATCHS. The table MATCH give for a gienv match the score of the player 1 (ID_PART_1) and the score of the player 2 (ID_PART_2). SCORE_1 is the score of player 1 (ID_PART_1). SCORE_2 is the score of player 1 (ID_PART_2). The tabel PARTICIPANTS contains the NAME and SURNAME of a player.

Here is the DDL the table PARTICIPANTS:

CREATE TABLE "PARTICIPANTS" 
  (    "ID_PARTICIPANT" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "NOM" VARCHAR2(100) NOT NULL ENABLE, 
    "PRENOM" VARCHAR2(100) NOT NULL ENABLE, 
    "DATE_NAISSANCE" DATE, 
    "GENRE" VARCHAR2(96), 
    "ID_CLUB" NUMBER, 
     PRIMARY KEY ("ID_PARTICIPANT")
 USING INDEX  ENABLE
  ) ;

Some data:

INSERT INTO PARTICIPANTS (ID_PARTICIPANT,NOM,PRENOM,DATE_NAISSANCE,GENRE,ID_CLUB) VALUES (1,'Dupont','Jean','1995-12-05','Homme',1);
INSERT INTO PARTICIPANTS (ID_PARTICIPANT,NOM,PRENOM,DATE_NAISSANCE,GENRE,ID_CLUB) VALUES (2,'Martin','Alice','1998-24-09','Femme',1);
INSERT INTO PARTICIPANTS (ID_PARTICIPANT,NOM,PRENOM,DATE_NAISSANCE,GENRE,ID_CLUB) VALUES (3,'DURAND','Thomas','1993-02-02','Homme',2);
INSERT INTO PARTICIPANTS (ID_PARTICIPANT,NOM,PRENOM,DATE_NAISSANCE,GENRE,ID_CLUB) VALUES (4,'Bernard','Chloé','2000-15-11','Femme',2);

DDL of the table MATCHS:

CREATE TABLE "MATCHS" 
  (    "ID_MATCH" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "ID_COMPETITION" NUMBER NOT NULL ENABLE, 
    "DATE_HEURE" DATE NOT NULL ENABLE, 
    "LIEU" VARCHAR2(200), 
    "ID_PART_1" NUMBER NOT NULL ENABLE, 
    "ID_PART_2" NUMBER NOT NULL ENABLE, 
    "SCORE_1" NUMBER(4,0) DEFAULT 0, 
    "SCORE_2" NUMBER(4,0) DEFAULT 0, 
    "STATUT" VARCHAR2(20) DEFAULT 'A jouer' NOT NULL ENABLE, 
     CONSTRAINT "CHK_MATCH_DIFF_PART" CHECK (id_part_1 != id_part_2) ENABLE, 
     CONSTRAINT "CHK_MATCH_SCORE1" CHECK (score_1 >= 0) ENABLE, 
     CONSTRAINT "CHK_MATCH_SCORE2" CHECK (score_2 >= 0) ENABLE, 
     PRIMARY KEY ("ID_MATCH")
 USING INDEX  ENABLE
  ) ;

Some data:

INSERT INTO MATCHS (ID_MATCH,ID_COMPETITION,DATE_HEURE,LIEU,ID_PART_1,ID_PART_2,SCORE_1,SCORE_2,STATUT) VALUES (61,41,'2026-05-06',42,3,1,0,0,'A jouer');
INSERT INTO MATCHS (ID_MATCH,ID_COMPETITION,DATE_HEURE,LIEU,ID_PART_1,ID_PART_2,SCORE_1,SCORE_2,STATUT) VALUES (41,1,'2026-05-06',41,4,1,0,0,'A jouer');
INSERT INTO MATCHS (ID_MATCH,ID_COMPETITION,DATE_HEURE,LIEU,ID_PART_1,ID_PART_2,SCORE_1,SCORE_2,STATUT) VALUES (22,1,'2026-05-06',41,1,4,0,3,'Terminé');
INSERT INTO MATCHS (ID_MATCH,ID_COMPETITION,DATE_HEURE,LIEU,ID_PART_1,ID_PART_2,SCORE_1,SCORE_2,STATUT) VALUES (81,41,'2026-05-06',42,4,1,2,3,'Terminé');

Here is a screen shot on in Interactive report for MATCHS to help better understand:

With the given data for player Chloé Bernard has a score of 2 (ID_PART1 = 4) for “Coupe du monde football”, and a score of 3 for “Tournoi de Roland Garros”. The total score (sum) for Chloé Bernanrd for all the competitions is 3+2 = 5.

Under APEX, the user whill choose a year in a drop-down list, and for year 2026 for example, the bar graph will display Name “Chloé Bernard” value : 5 (and of course the other players names and total scores).

Best regards.