CODEWITHSUNDEEP
sqlvb6ado
user2125649
user2125649

Reputation: 9

SUM of a field within one table, GROUP BY a field in a second table

I found how to use SUM and GROUP BY within one table. What I try to do is to sum one field (Marks.m_GPA) in one table depending on a field in a second table (Curriculum.[c_Subject Group]).

I have one table with all the results of the students named Marks and another table named Curriculum. Both tables have the subject code in common (c_Code and m_Code), the Curriculum table has got one field [c_Subject Group]. I want to have the sum of all m_GPA values for each [c_Subject Group] and the results GROUP BY [c_Subject Group].

All fields are strings (text).

It's an ADO SQL VB6 application where I use the MS Jet engine of Access. I usually try to run a query using Access to test the query beforehand, ACCESS seems to accept my syntax, but the result is an empty table.

Here is the query: 

SELECT 
   Curriculum.[c_Subject Group], 
   Sum([m_GPA]) AS Total_GPA 
FROM Curriculum 
   INNER JOIN Marks ON 
      Curriculum.c_Code = Marks.m_Code 
WHERE Curriculum.[c_Subject Group]= "1"
GROUP BY Curriculum.[c_Subject Group];

Even if I try Sum(cdbl(Marks.[m_GPA])), the result is an empty table.

Upvotes: 0

Views: 853

Answers (1)

Snippet
Snippet

Reputation: 1560

Try this one.

SELECT 
     Curriculum.[c_Subject Group],
     Marks.Total_GPA
 FROM Curriculum
 LEFT JOIN (
     SELECT 
     m_Code,
     Sum([m_GPA]) AS Total_GPA
     FROM Marks
     GROUP BY m_Code
)Marks
 ON Curriculum.c_Code = Marks.m_Code 
 WHERE Curriculum.[c_Subject Group]= '1'

Upvotes: 1

Related Questions