CODEWITHSUNDEEP
sqlsql-server
nothrow
nothrow

Reputation: 16178

How to properly compute weighted average for zeroes in SQL

I have following problem - I'm computing weighted average in SQL, as following: SUM(Value * Weight) / SUM(Weight). However, there can be issue that rows are empty => SUM(Weight) == 0), and in this case the query fails. Is it somehow possible to return '0' as result in this case?

I have tried CASE SUM(Weight) WHEN 0 THEN 0 ELSE SUM(Value * Weight) / SUM(Weight) END, but I'm afraid that it evaluates SUM(Weight) twice, and that can be fairly expensive in my case.

Upvotes: 2

Views: 807

Answers (2)

Lamak
Lamak

Reputation: 70668

Use NULLIF and ISNULL:

ISNULL(SUM(Value * Weight) / NULLIF(SUM(Weight),0),0)

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74345

The SQL engine doesn't compute sum(Weight) twice, just once. The conceptual process is:

  • compute the full cartesian join of all tables in the from clause
  • apply the join criteria to filter the results
  • apply the where clause criteria to filter the results
  • partition this result set into groups as defined by the group by clause
  • collapse each such group into one row, computing any aggregate functions that have been specified, keeping only those columns listed in the result set (aggregrate functions and grouping columns),
  • apply the criteria in the having clause to filter the grouped results,
  • drop all columns but those specified in the queries result columns, creating those that are computed expressions.
  • apply the ordering specified in the order by statement.

No actual SQL engine does this, but it must behave as if that is what happened. Your aggregate function is computed just once, along with any other aggregate functions, in a single pass.

Upvotes: 1

Related Questions