On Monday, 23 December 1991 at 12:15:42 UTC-8, Jon Rosen wrote:
In article <01GEG2QDS...@NAUVAX.UCC.NAU.EDU> DEV...@NAUVAX.UCC.NAU.EDU (Ernie DeVries) writes:
I have what seems like a relatively simple task - construct a query to calculate
the cumulative grade point average for each student. Although it seems simple,
I am having a lot of trouble getting a query to do it. My guess is that there
is something simple that I'm missing so your help is appreciated.
My sample table contains 3 fields:
ID A4
Grade N
Hours N
I have successfully produced a report that calculates the cumulative gpa by >grouping on ID and placing this calculated field into the group footer: >sum([Grade]*[Hours],Group)/sum([Hours],Group)
The challenge is to figure out how to do the same thing in a query.
Well I am sort of in the dark here because you don't state what
kind of DBMS you are using. I surmise from the description of
the report statement you are using that you are using SQL of
some flavor, possibly Ingres or Oracle. I will try to answer
the question (to the extent it can be answered) from an ANSI
SQL perspective.
If what you want is a simple query that gives you ONLY cumulative
grade point average for each student, then you are doing a simpl
weighted average calculation and the solution is similar to the
one you used for the report:
SELECT ID, SUM(G*H)/SUM(H) FROM STUDENT
GROUP BY ID
ORDER BY ID (this is only needed to get the students in ID order)
I have left out any WHERE clause or any problems with NULLs (although
this is not really a problem... see other posts on nulls).
If, however, you want both detail AND cumulative GPA in a single
query, you are reaching into a problem realm for ANSI SQL.
In current ANSI SQL, there is no way to create a GROUP BY for part
of a query and not for another (or to create two different levels
of GROUP BY in a single query). Thus, ANSI SQL prohibits the
following query:
SELECT ID, G, H, SUM(G*H)/SUM(H)
GROUP BY ID
since ANSI SQL requires that all columns that are NOT aggregated
MUST be used in the GROUP BY clause OR must be constant.
There are several SQL implementations (Sybase, I believe for one)
which can do this because of SQL extensions which are not standard.
Also, SQL2 may have a way of handling this.
Today, the best solution is to create a temp table called CUM
and do the following:
INSERT INTO CUM SELECT ID, SUM(G*H)/SUM(H) FROM STUDENT GROUP BY ID
then:
SELECT S.ID, C.GPA. S.G, S.H FROM STUDENT S, CUM C
WHERE S.ID = C.ID
This will get both of the sets of numebrs into a query.
Jon
Use select statement to find the following
1. Average GPA of all students
2. Average GPA of Finance and CIS students
3. Give the name of the student with the highest GPA
4. Show the students with the GPA grades in each major
5. Show the students with the GPA grades in each major ordered by GPA in descending order
6. Provide a listing of each student and the name of their tutor (Recursive query)
7. How many students does each tutor work with?
8. Add .05 to all of the Marketing major's GPA's
9. Change Sam's tutor from Sue to Jane
10. For any student who is currently majoring in CIS and who has a GPA of less than 3.0,
change their major to Marketing
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)