CODEWITHSUNDEEP
sqlms-accessms-access-2013
FSm
FSm

Reputation: 2057

Dividing summed field by another summed field in the same query

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

Upvotes: 0

Views: 6440

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112352

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:
enter image description here

(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;

Upvotes: 3

Related Questions