Calculate average sale value for the result of the sql query

Question

Post reply

Calculate average sale value for the result of the sql query

tswapnars 90250

SSC Enthusiast

Points: 124
More actions
May 23, 2013 at 6:25 pm

#274198

Can anyone please tell me how to calculate the Average Value and select it as a column?
I am trying to add AVG(TotalValue) as AgvSales on the top of query but throws error
Column 'SalesData.SaleDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

DBA328 Hall of Fame Points: 3952 More actions · Answer 1

You missed group by, here is one example:

select EmployeeKey,AVG(SalesAmountQuota) as Amout_Avg

from [dbo].[FactSalesQuota]

group by EmployeeKey

DBA328 Hall of Fame Points: 3952 More actions · Answer 2

You missed group by, here is one example:

select EmployeeKey,AVG(SalesAmountQuota) as Amout_Avg

from [dbo].[FactSalesQuota]

[highlight=#ffff11] group by EmployeeKey[/highlight]

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 3

Any select query with aggerate function requires a GROUP By clause with columns list to be grouped on.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Bhaskar.Shetty (5/23/2013)
Any select query with aggerate function requires a GROUP By clause with columns list to be grouped on.

Not quite true. Many of the aggregate functions can be used with the OVER clause. It may not give the answer you want because it operates slightly differently (depends on how the partioning of the OVER clause is setup), but it doesn't require a GROUP BY.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

tswapnars 90250 (5/23/2013)
Can anyone please tell me how to calculate the Average Value and select it as a column?
I am trying to add AVG(TotalValue) as AgvSales on the top of query but throws error
Column 'SalesData.SaleDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please post the query. Just adding a GROUP BY to a query where you didn't plan for it can lead to a real world of hurt.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)