Why does my Count stay at 1 and instead counts for each column?

Question

So for my assignment I have to get the one (given there's one) professor that supervises all the projects in the summer semester 2020. My Idea was to just count the amount of supervising professors. If it's "1" then that professors name gets selected, but if there's 2 or more professors then no one gets selected. But when I test my code, I get 2 different Professors with each having their count on "1"...

Code looks like this

SELECT DISTINCT Nachname, Vorname, COUNT (DISTINCT proj.prof_persID) AS Professoren
FROM Personal pers
JOIN Projekte proj
ON pers.persID = proj.prof_persID
WHERE proj.Semester = 'SS2020'
GROUP BY Nachname, Vorname
HAVING COUNT (proj.prof_persID) = 1;

Pointing me in the right direction surely suffices, thx!

EDIT

Example for the Tables themselves

CREATE TABLE Personal (
 PersID               INTEGER      GENERATED ALWAYS AS IDENTITY
                                   CONSTRAINT PersPK PRIMARY KEY,
 Nachname             VARCHAR2(30) NOT NULL ,
 Vorname              VARCHAR2(30) NOT NULL ,
 Telefonnr            VARCHAR2(15) NULL ,
 Email                VARCHAR2(50) NOT NULL 
            CONSTRAINT pEmailSyntax CHECK (email LIKE '%@%.__'
                            OR email LIKE '%@$.___')
                        INITIALLY DEFERRED,
 Raum                 VARCHAR2(5)  NULL ,
 Typ                  CHAR(1)      NOT NULL
                      CONSTRAINT   PersTypChk CHECK ( UPPER(typ) IN
                                   ('W','P','S','V') ) INITIALLY IMMEDIATE,
 von                  DATE,
 bis                  DATE);

CREATE TABLE Projekte (
 ProjID               INTEGER      NOT NULL ,
 Bezeichnung          VARCHAR2(30) NOT NULL ,
 Semester             VARCHAR2(10) NOT NULL ,
 maxGGroesse          INTEGER      NULL ,
 Fach                 VARCHAR2(20) NULL ,
 WMA_PersID           INTEGER      NULL ,
 Prof_PersID          INTEGER      NULL ,
 Freigeschaltet       DATE         NULL ,
    CONSTRAINT PKProjekte PRIMARY KEY (ProjID),
    CONSTRAINT freischaltenCck CHECK ( (freigeschaltet IS NOT NULL
                                AND Fach IS NOT NULL AND Prof_persID IS NOT NULL)
                                OR freigeschaltet IS NULL ) INITIALLY DEFERRED );

//SAMPLE DATA FOR "PERSONAL"

INSERT INTO Personal (Nachname, Vorname, Telefonnr, Email, Raum, Typ, von, bis)
     VALUES ('Bauer', 'Erna', '02261 81961211', 'EB@thk.de', '2230', 'P', TO_DATE('01.03.2019', 'DD.MM.RRRR'), NULL);
INSERT INTO Personal (Nachname, Vorname, Telefonnr, Email, Raum, Typ, von, bis)
     VALUES ('Mecker', 'Else', '02261 81964222', 'EM@thk.de', '2254', 'P', TO_DATE('16.06.2020', 'DD.MM.RRRR'), TO_DATE('20.02.2022', 'DD.MM.RRRR'));

//SAMPLE DATA FOR "PROJECTS"

INSERT INTO Projekte 
     VALUES (proj_seq.NEXTVAL, 'Oracle-Praktikumsprojekt', 'SS2020', 2, 'DBS1', NULL, 2, TO_DATE ('15.04.2020', 'DD.MM.RRRR'));
INSERT INTO Projekte 
     VALUES (proj_seq.NEXTVAL, 'Build-A-Bear Workshop', 'SS2020', 2, 'DBS1', NULL, 3, TO_DATE ('15.04.2020', 'DD.MM.RRRR'));

As for the two example samples for my projects, I expected my program to select none, since there's multiple professors overseeing atleast one of the projects in 'SS2020', but it selects both given professors with each having a count of 1.

user_2445629 · Answer

The fiddle

Terms to review:

aggregate function
functional dependence

Given GROUP BY name, this generates a result containing one row for each distinct name value found. So if there were 2 professors each with a different name (example: 'prof1' and 'prof2'), GROUP BY name would generate a result for each of those groups, and your subsequent COUNT(DISTINCT prof_id) expression would just find one professor in each group, the id of 'prof1' in group 1 and the id of 'prof2' in group 2.

Basically, you didn't want to include FirstName or LastName in your GROUP BY terms, since that causes each professor with a different name to form a separate group in your results. You wanted to just do something like this, over all professors in the chosen Semester:

SELECT proj.Semester
     , MIN(LastName) AS LastName, MIN(FirstName) AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projects proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
 GROUP BY proj.Semester
HAVING COUNT (DISTINCT proj.prof_persID) = 1
;

The result:

SEMESTER	LASTNAME	FIRSTNAME	PROFESSORS
SS2020	Mecker	Else	1

I left in the GROUP BY clause and included just Semester, so you could expand the list of semesters and obtain the results for more than one semester at once.

You can also just remove the GROUP BY clause, which then operates on all chosen rows as one group. Adjust the select list, if you do that.

Like this:

SELECT MIN(proj.Semester) AS Semester
     , MIN(Nachname) AS LastName, MIN(Vorname) AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projekte proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
HAVING COUNT (DISTINCT proj.prof_persID) = 1
;

or just this:

SELECT MIN(Nachname) AS LastName, MIN(Vorname) AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projekte proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
HAVING COUNT (DISTINCT proj.prof_persID) = 1
;

Your original query:

SELECT Nachname AS LastName, Vorname     AS FirstName
     , COUNT (DISTINCT proj.prof_persID) AS Professors
  FROM Personal pers
  JOIN Projekte proj
    ON pers.persID = proj.prof_persID
 WHERE proj.Semester = 'SS2020'
 GROUP BY Nachname, Vorname
HAVING COUNT ( proj.prof_persID ) = 1
;

Resulted in this (I added a second professor row to the data):

SEMESTER	LASTNAME	FIRSTNAME	PROFESSORS
SS2020	Mecker	Else	1
SS2020	Mecker2	Else2	1

Updated fiddle with extra professor

See the concept of aggregate functions.

MIN is an aggregate function as used above.

In this case, it lets us obtain/extract other columns/expressions from the group.

In any group of professors (in this semester), there could be many names.

MIN(name) just picks the name that is lexicographically / alphabetically first in that group of names.

In your specific problem, at most one professor will be found, so MIN(name) will be that one professor's name from that group.

Why does my Count stay at 1 and instead counts for each column?

1 Answers