Recent

Author Topic: [SOLVED]Counting the games per Referee in a season and if no games then add zero  (Read 2364 times)

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
I have 3 table in a database
One contains the data about the games that are played and is defined as followed:
Code: SQL  [Select][+][-]
  1. CREATE TABLE tbl_Wedstrijden (Wed_ID Word PRIMARY KEY ASC, Wed_Seizoen_ID Word, Wed_Datum DATE, Wed_Tijd TIME, Wed_Plaats Word, Wed_TeamA Word, Wed_TeamB Word, Wed_Scheids_1 Word, Wed_Scheids_2 Word, Wed_Scheids_3 Word, Wed_Scheids_4 Word, Wed_Divisie Word, Wed_Begeleiding BOOLEAN, Wed_SPV_ID Word, Wed_Opmerkingen Text, Wed_Meetellen BOOLEAN, Wed_Show BOOLEAN, Wed_Outlook BOOLEAN, FOREIGN KEY(Wed_Seizoen_ID) REFERENCES "tbl_Seizoenen_Oud"(Sei_ID), FOREIGN KEY(Wed_TeamA) REFERENCES tbl_Teams(Team_ID), FOREIGN KEY(Wed_TeamB) REFERENCES tbl_Teams(Team_ID), FOREIGN KEY(Wed_Scheids_1) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_2) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_3) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Scheids_4) REFERENCES tbl_Scheidsrechters(Scheids_ID), FOREIGN KEY(Wed_Divisie) REFERENCES tbl_Divisie(Divisie_ID), FOREIGN KEY(Wed_SPV_ID) REFERENCES tbl_Supervisors(SPV_ID))


I have a table with licenses for the referees. It is defined as:
Code: SQL  [Select][+][-]
  1. CREATE TABLE tbl_Licenties (Fluit_ID Word PRIMARY KEY ASC, Fluit_Seizoen_ID Word, Fluit_Scheids_ID Word, Fluit_Licentie_ID Word, Fluit_Cursus BOOLEAN, Fluit_Opmerking TEXT, Fluit_Vergoeding BOOLEAN, Fluit_Bedrag FLOAT)

And i have a table for the referees. It is defined as:
Code: SQL  [Select][+][-]
  1. CREATE TABLE tbl_Scheidsrechters (Scheids_ID word PRIMARY KEY ASC, Voornaam VARCHAR(20), Tussenvoegsel VARCHAR(10), Achternaam VARCHAR(50), Adres VARCHAR(60), Postcode VARCHAR(6), Woonplaats VARCHAR(40), Telefoon VARCHAR(11), Mobiel VARCHAR(11), Geboortedatum DATE, Email VARCHAR(250), IBAN VARCHAR(18))

How can i count the games of each referee and if the referee has NO GAMES then a ZERO has to be assigned to that referee in that season.
So the final view has to be something like this:


Scheids_ID | Fluit_Seizoen_ID | Games

« Last Edit: February 22, 2018, 12:00:55 am by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Counting the games per Referee in a season and if no games then add zero
« Reply #1 on: February 21, 2018, 04:01:20 pm »
Think you would need some combination of Count and Coalesce functions possibly in a GROUP BY query. These are standard SQL functions and syntax.

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Counting the games per Referee in a season and if no games then add zero
« Reply #2 on: February 21, 2018, 06:55:34 pm »
Thanks for the though...
I kept playing around with some INNER JOIN's en LEFT JOIN's and finally came up with the solution.
Code: SQL  [Select][+][-]
  1. SELECT Fluit_Seizoen_ID, Fluit_Scheids_ID, qry_Temp_ScheidsrechtersPerSeizoen.Scheidsrechter AS Scheidsrechter,
  2. SEI_Periode AS Periode, Fluit_Licentie_ID, Licentie, CASE WHEN Keer ISNULL THEN 0 ELSE qry_Temp_Seizoen_GeflotenWedstrijden.Keer END AS Evals
  3. FROM (qry_Temp_ScheidsrechtersPerSeizoen LEFT JOIN qry_Temp_Seizoen_GeflotenWedstrijden ON
  4. (qry_Temp_ScheidsrechtersPerSeizoen.SEI_Periode = qry_Temp_Seizoen_GeflotenWedstrijden.Periode) AND
  5. (qry_Temp_ScheidsrechtersPerSeizoen.Fluit_Scheids_ID = qry_Temp_Seizoen_GeflotenWedstrijden.Scheids_ID))
  6. INNER JOIN tbl_Licenties_Keuze ON qry_Temp_ScheidsrechtersPerSeizoen.Fluit_Licentie_ID = tbl_Licenties_Keuze.Licentie_ID
  7. GROUP BY 2,4
« Last Edit: February 21, 2018, 06:57:10 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Counting the games per Referee in a season and if no games then add zero
« Reply #3 on: February 21, 2018, 07:32:19 pm »
CASE WHEN Keer ISNULL THEN 0 ELSE qry_Temp_Seizoen_GeflotenWedstrijden.Keer END AS Evals

can be rewritten as

COALESCE(qry_Temp_Seizoen_GeflotenWedstrijden.Keer, 0) AS Evals

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Counting the games per Referee in a season and if no games then add zero
« Reply #4 on: February 22, 2018, 12:00:43 am »
Didn't know that.... Thanx

You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

 

TinyPortal © 2005-2018