[SOLVED]Counting the games per Referee in a season and if no games
then add zero
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2018-02-21 10:03

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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

----------------------------------------------------------------------
goodname · 2018-02-21 15:01

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 · 2018-02-21 17:55

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
Fluit_Seizoen_ID, Fluit_Scheids_ID,
qry_Temp_ScheidsrechtersPerSeizoen.Scheidsrechter AS Scheidsrechter,
SEI_Periode AS Periode, Fluit_Licentie_ID, Licentie, CASE WHEN Keer
ISNULL THEN 0 ELSE qry_Temp_Seizoen_GeflotenWedstrijden.Keer END AS
Evals FROM (qry_Temp_ScheidsrechtersPerSeizoen LEFT JOIN
qry_Temp_Seizoen_GeflotenWedstrijden ON
(qry_Temp_ScheidsrechtersPerSeizoen.SEI_Periode =
qry_Temp_Seizoen_GeflotenWedstrijden.Periode) AND
(qry_Temp_ScheidsrechtersPerSeizoen.Fluit_Scheids_ID =
qry_Temp_Seizoen_GeflotenWedstrijden.Scheids_ID)) INNER JOIN
tbl_Licenties_Keuze ON
qry_Temp_ScheidsrechtersPerSeizoen.Fluit_Licentie_ID =
tbl_Licenties_Keuze.Licentie_ID GROUP BY 2,4

----------------------------------------------------------------------
goodname · 2018-02-21 18:32

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 · 2018-02-21 23:00

Didn't know that.... Thanx