CODEWITHSUNDEEP
sqlsql-servert-sqlsubqueryaggregate-functions
Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

SQL-Server. Calculate formula with subquery in aggregate function

I need to calculate value of Quantity column where Name = A using this formula in SQL-Server:

A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E

Sample data:

Static  Static   Dynamic   
 Name     ID     Quantity  -- Here are more columns, but doesn't matter
 A        1         ? -- need to calculate this value
 B        2         4
 C        3         6
 D        4         8
 E        5         5
 A1       6         -
 B1       7         2
 C1       8         3
 D1       9         4

Have you any ideas how to do It? All values in Quantity column are dynamic, Name and Id columns are static.

I think something like below, but there is PROBLEM that subqueries can't be used in aggregate function:

SELECT Name,
       Id,
       ISNULL(MAX(CASE WHEN f.Period = YEAR(GETDATE())*100 + 01 THEN
                       CASE WHEN Name = 'A' THEN 
                              (SELECT Quantity FROM Cte WHERE Name = 'A' ) *
                              (SELECT Quantity FROM Cte WHERE Name = 'A1') +
                              (SELECT Quantity FROM Cte WHERE Name = 'B' ) *
                              (SELECT Quantity FROM Cte WHERE Name = 'B1') 
                               ................... 
                            ELSE Quantity 
                       END 
                   END), 0) as Quantity
FROM tbl...

I got following error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Have you any ideas how can I use subquery in aggregate function or any other way to calculate It?

Upvotes: 2

Views: 660

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I would be inclined to use conditional aggregation to get the values of the variables:

select max(case when name = 'B' then quantity end) as B,
       . . .,
       max(case when name = 'D1' then quantity end) as D
from sample s;

You can then incorporate this into an update:

with variables as (
      select max(case when name = 'B' then quantity end) as B,
             . . .,
             max(case when name = 'D1' then quantity end) as D
      from sample s
     )
update s
    set s.value = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E
    from sample s cross join
         variables v
    where name = 'A';

Note: there are other ways to pivot the data; conditional aggregation is just one approach (using pivot, using multiple joins).

Upvotes: 3

Related Questions