Dividing summed field by another summed field in the same query

Question

How to divide a summed field by another summed field in the same query.

Example: lets have the query "querySummary" which its field have been grouped already

  SID      SumOfCredits     SumOfMarks
  1            3                18
  2            2                20
  3            4                40
Group By      Sum               Sum

I want to add another field named "FAvg" to the same query that builds up of dividing "SumOfMarks" by SumOfCredits, so the the result should be as following

SID      SumOfCredits     SumOfMarks      FAvg
1            3                18           6
2            2                20           10
3            2                40           20

Any help please ? many Thanks

Answer 1

Replace "Sum" in the "Total" row by "Expression" and in the "Field" row use the expression:

FAvg: Sum(Mark)/Sum(Credit)

You'll get something like this:

(The other Sum columns are not required for the FAvg expression)

The SQL looks like this:

SELECT
    Table1.SID,
    Sum(Table1.Credit) AS SumOfCredit,
    Sum(Table1.Mark) AS SumOfMark,
    Sum([Mark])/Sum([Credit]) AS FAvg
FROM
    Table1
GROUP BY
    Table1.SID;