CODEWITHSUNDEEP
sqlsql-server
Beto A. Lien
Beto A. Lien

Reputation: 43

Solve on SUM and AVG, Cannot perform an aggregate function on an expression containing an aggregate or a subquery

I have the following query in SQL to Sum and Average values:

USE DBSTG_INT
GO

select distinct 
sum(a.EventoPrecioReg) as PrecioRegular,
sum(a.EventoPrecioVta) as PrecioVenta,
AVG(VTA_IUnidades) as Unidades,
SUM((a.EventoPrecioVta - a.EventoPrecioReg) * (AVG(VTA_IUnidades))) as Inversion
from EventoPrecioDeta a WITH (NOLOCK)

And when I execute it, it shows following problem:

Msg 130, Level 15, State 1, Line 8
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

What I have done wrong and how can I solve it?

Upvotes: 2

Views: 1472

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below dont need distinct 

select  
sum(a.EventoPrecioReg) as PrecioRegular,
sum(a.EventoPrecioVta) as PrecioVenta,
AVG(VTA_IUnidades) as Unidades,
SUM(a.EventoPrecioVta - a.EventoPrecioReg) * AVG(VTA_IUnidades) as Inversion
from EventoPrecioDeta a

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

select distinct doesn't make sense in this case. Your problem is nested aggregates. Perhaps you intend:

select sum(a.EventoPrecioReg) as PrecioRegular,
       sum(a.EventoPrecioVta) as PrecioVenta,
       AVG(VTA_IUnidades) as Unidades,
       SUM(a.EventoPrecioVta - a.EventoPrecioReg) * AVG(VTA_IUnidades) as Inversion
from EventoPrecioDeta a ;

Upvotes: 1

Related Questions