CODEWITHSUNDEEP
sqlsql-servert-sql
Ehsan
Ehsan

Reputation: 2285

AVG on more than one column

I have a table that in one column saved grade of a course and in another column save the ratio of that course, then I want to calculate average of courses with ratio, I write these code but I faces with error, please help me

select SUM((grade * ratio) / SUM(ratio)) as averageOfCourses
from myTable

Upvotes: 1

Views: 186

Answers (3)

Phil
Phil

Reputation: 42991

declare @sum float = (select sum(ratio) from myTable)
select sum( grade * ratio / @sum ) as averageOfCourses from myTable

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176896

try

select (SUM(grade * ratio) / SUM(ratio)) as averageOfCourses from myTable

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753795

Your problem is probably misplaced parentheses.

SELECT SUM(grade * ratio) / SUM(ratio) AS averageOfCourses
  FROM myTable

If you truly want to do a SUM of the values divided by a SUM, then you have to work a good deal harder; you can't do aggregates of aggregates directly.

To do what you wrote, you'd have to rewrite it as:

SELECT SUM((grade * ratio) / sum_ratio) AS averageOfCourses
  FROM (SELECT grade, ratio, SUM(ratio) AS sum_ratio
          FROM myTable
         GROUP BY grade, ratio) AS precalculation

(The final AS is required by standard SQL, but doesn't contribute anything to this query.)

In this context, I'm not even sure it gives a different answer.

Upvotes: 1

Related Questions