Trouble With My GROUP BY

[goslincm]

I am running this query below, but not getting what I'm after. I want to get aggregrate information. For any instructor who is or has taught more than one class in one semester, I want to list that facid, the count of the classes, and a sum of the curenrol for the class.

My results give me the facid for each class section and each semester. I'm not properly grouping this by sections and semester dates.

SELECT Faculty.FacId, Section.SectNumb, Section.SemDate

FROM (Faculty
INNER JOIN [Section] ON Faculty.FacId = Section.FacId) INNER JOIN Enroll ON (Section.SemDate = Enroll.SemDate) AND (Section.SectNumb = Enroll.SectNumb)

WHERE (((Enroll.SectNumb)>"1") AND ((Enroll.SemDate)>1))
GROUP BY Faculty.FacId, Section.SectNumb, Section.SemDate;

 

[lespaul]

where's your aggregate function (sum, avg, max)?

Do you need to count the number of records in Enroll to find the Curenrol for the course? Is the count of classes calculated by counting the FacId in the Section table?

[tt]
Faculty
FacID FacName


Section
CourseID SectNumb Semester


Enroll
StudentID SectNumb SemDate


Desired Results
FacID SectNumb SemDate CountOfEnroll CountOfClass

[/tt]

If you could fill in sample data and expected results, that would make it much easier to help.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Thanks for replying Leslie.

Enroll table contains the stuid, sectionnumber, semdate and grade.

Section table contains sectionnumber, semesterdate, facid, curenrol

Faculty table contains facid, facname, facstate

What I need to do is for EACH instructor who has taught or is teaching more than 1 class section during one semester, list the semester date, facid, number of classes taught and total number of students.

Does that explain it any better?

I didn't

 

[randy700]

Is your Enroll.SectNumb field text or numeric?


Randy

 

[goslincm]

Randy, its a text field

 

[PHV]

You wanted something like this ?
SELECT S.semesterdate, S.FacId, C.Classes, Count(*) AS Students
FROM (Enroll AS E
INNER JOIN Section AS S ON E.sectionnumber = S.sectionnumber AND E.semdate = S.semesterdate)
INNER JOIN (
SELECT FacId, semesterdate, Count(*) AS Classes FROM Section GROUP BY FacId, semesterdate HAVING Count(*)>1
) AS C ON S.FacId = C.FacId AND S.semesterdate = C.semesterdate
GROUP BY S.semesterdate, S.FacId, C.Classes

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goslincm]

PH, what is the C.Classes representing? section number?

 

[PHV]

The number of classes, as you asked 4 May 07 18:13.

 

[goslincm]

Sorry getting lost in this I guess.

 

[PHV]

Have you even tried the SQL code I suggested you ?

 

[goslincm]

PHV, you bet I tried it and its working exactly as I needed it to. I was only asking things because I need to understand and "see" everything as best I can.

 

[PHV]

I need to understand and "see" everything as best I can
Question:
[!]for EACH instructor who has taught or is teaching more than 1 class section during one semester[/!], list the semester date, facid, [highlight]number of classes taught[/highlight] and total number of students

Answer:
SELECT S.semesterdate, S.FacId, [highlight]C.Classes[/highlight], Count(*) AS Students
FROM (Enroll AS E
INNER JOIN Section AS S ON E.sectionnumber = S.sectionnumber AND E.semdate = S.semesterdate)
INNER JOIN (
[!]SELECT FacId, semesterdate, [highlight]Count(*) AS Classes[/highlight] FROM Section GROUP BY FacId, semesterdate HAVING Count(*)>1[/!]
) AS C ON S.FacId = C.FacId AND S.semesterdate = C.semesterdate
GROUP BY S.semesterdate, S.FacId, [highlight]C.Classes[/highlight]

 

[randy700]

I think goslincm is confused by the alias C.

In the FROM clause, PHV uses aliases to represent the table names....
Enroll AS E E is the Enroll table
Section AS S S is the Section table
SELECT..... AS C C is the query

C.Classes is the result (Count) from the sub-query.

Randy