calculated field in SELECT statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculated field in SELECT statement?

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-11 : 02:14:59

I need an example please.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-11 : 02:30:17

SELECT SUM(F.SalesAmount) AS SumSalesAmt, (F.SalesAmount * 1) AS CalculatedAmt
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D
ON F.ProductKey = D.ProductKey
WHERE D.EnglishProductName = 'BK-R64Y-42'

When I execute the above code I get an error: Column 'dbo.FactInternetSales.SalesAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-11 : 05:29:28

once you apply aggregation on field, you cant access the inidvidual values of same field. so you need to do either of the below


SELECT SUM(F.SalesAmount) OVER () AS SumSalesAmt, (F.SalesAmount * 1) AS CalculatedAmt
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D
ON F.ProductKey = D.ProductKey
WHERE D.EnglishProductName = 'BK-R64Y-42'

or


SELECT SUM(F.SalesAmount) AS SumSalesAmt, SUM(F.SalesAmount * 1) AS CalculatedAmt
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D
ON F.ProductKey = D.ProductKey
WHERE D.EnglishProductName = 'BK-R64Y-42'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources