CODEWITHSUNDEEP
sqlsql-serversumaverage
pbars23
pbars23

Reputation: 95

SQL Server: avg sum

I'm trying to find the average of the sum of a column. With the code below:

select  avg(sum(totalSpend)) as HHtypetotalSpend,
        HHtype 
from dbo.ANTHEM_IDS_JR 
GROUP BY HHtype, totalSpend;

I get there error:

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

Is there a way to get the average of the sum of the columns with the built in avg() function, or do I need to count the rows and divide the sum by the number of rows?

AFTER EDIT:

Here's my code thanks to some answers to my question:

  SELECT SUM(totalSpend)/SUM(numVisits) AS totalSpend, HHtype
  FROM dbo.ANTHEM_IDS_JR 
  GROUP BY HHtype

But I get this error message:

Divide by zero error encountered.

Any idea on how to fix this? Should I create a function to remove the rows with 0 in the numVisits?

AFTER ANOTHER EDIT:

Here is my code now. It is a function:

DROP FUNCTION getHHtypeTotalSpendAvg

GO
CREATE FUNCTION getHHtypeTotalSpendAvg()
RETURNS float
BEGIN
DECLARE @HHtypetotalSpendAvg float
DECLARE @ANTHEM_IDS_JR_TABLE table (numVisits int, totalSpend float)

INSERT into @ANTHEM_IDS_JR_TABLE select numVisits as numVisits, totalSpend as totalSpend from dbo.ANTHEM_IDS_JR--(dbo.ANTHEM_IDS_JR.numVisits, dbo.ANTHEM_IDS_JR.totalSpend)
-- SET @ANTHEM_IDS_JR_TABLE=dbo.ANTHEM_IDS_JR.numVisits, dbo.ANTHEM_IDS_JR.totalSpend
-- select @HHtypetotalSpendAvg = sum(totalSpend)/sum(numVisits), HHtype from dbo.ANTHEM_IDS_JR GROUP BY HHtype;
DELETE numVisits from @ANTHEM_IDS_JR_TABLE WHERE numVisits=0
DELETE totalSpend from @ANTHEM_IDS_JR_TABLE WHERE totalSpend=NULL
select @HHtypetotalSpendAvg = sum(totalSpend)/sum(numVisits) from dbo.ANTHEM_IDS_JR GROUP BY HHtype;

RETURN @HHtypetotalSpendAvg
END;

GO
EXEC getHHtypeTotalSpendAvg`

But i get the error message:

Invalid object name 'numVisits'

Any help would be appreciated.

Upvotes: 1

Views: 253

Answers (2)

potashin
potashin

Reputation: 44601

You can't use one aggregate function in another :

  SELECT (CASE WHEN SUM(numVisits) = 0 THEN 0 ELSE SUM(totalSpend)/SUM(numVisits) END) AS totalSpend
       , HHtype
  FROM dbo.ANTHEM_IDS_JR 
  GROUP BY HHtype, totalSpend

Upvotes: 1

veritaS
veritaS

Reputation: 519

AVG of a Sum does not make much sense In my opinion, because if you already have created the sum you only have 1 value from which you are creating the AVG. You will always get the same AVG as the SUM

Upvotes: 0

Related Questions