CODEWITHSUNDEEP
sqlsql-serverms-access
maggs
maggs

Reputation: 125

Error with converting access query to SQL Server 2012 query

I am converting queries from Access 1010 into SQL Server 2012. The following is part of a query

Count(Stats.SessionNumber) AS TotalSessions, Sum(Stats.Duration) AS TotalDuration, 
Round([TotalDuration]/[TotalSessions],1) AS AverageDuration

I get the following Error: Invalid column name 'TotalDuration'. Invalid column name 'TotalSessions'.

Does the alias of TotalDuration and TotalSessions need to be handled differently in SQL Server and if so how?

thanks

Upvotes: 0

Views: 32

Answers (2)

M.Ali
M.Ali

Reputation: 69514

You can do something like this....

SELECT  TotalSessions
       ,TotalDuration
       ,Round([TotalDuration]/[TotalSessions],1) AS AverageDuration
FROM (
        SELECT  Count([Stats].SessionNumber) AS TotalSessions
               , SUM([Stats].Duration) AS TotalDuration
        FROM Table_Name
     ) A

Upvotes: 2

comfortablydrei
comfortablydrei

Reputation: 316

In SQLServer, you can't work on an alias you just assigned. You either need to use a sub query, or reuse your SUM and COUNT.

Re-use:

Count(Stats.SessionNumber) AS TotalSessions, Sum(Stats.Duration) AS TotalDuration, 
Round(Sum(Stats.Duration)/Count(Stats.SessionNumber),1) AS AverageDuration

Subquery:

SELECT TotalSessions, TotalDuration, Round(TotalDuration/TotalSessions,1) AS AverageDuration
FROM
(
  SELECT RCount(Stats.SessionNumber) AS TotalSessions, Sum(Stats.Duration) AS TotalDuration, 
  FROM yourTableName
 ) subquery

Upvotes: 1

Related Questions