CODEWITHSUNDEEP
sqlsql-servert-sql
Mark Gilliam
Mark Gilliam

Reputation: 13

no column was specified for column [x] of [table] and invalid column name

When running the following query, I get the desired results, but SSMS indicates that "no column was specified for column 2 of 'MonthlyTotals' when I mouse over the table alias and when I mouse over the "Totals" in the AVG function, it specifies that it's an invalid column name.

SELECT AVG(Totals) as 'MonthlyAvg' from
(
SELECT [DATE], SUM(Assets) as 'Totals'
FROM Assets inner join Funds on Assets.FundCode = Funds.FundCode
WHERE FeeGroupID = 17
    and (([Date] >= '1/1/2013') 
    AND ([Date] <= '4/1/2013')) 
    and ((Funds.EndDt >= '4/1/2013') OR (Funds.EndDt is null))
GROUP BY [DATE]
) as MonthlyTotals

Upvotes: 1

Views: 5278

Answers (1)

granadaCoder
granadaCoder

Reputation: 27862

Total guess, but try this alternate syntax.

SELECT [MonthlyAvg] = AVG(Totals) from
(
SELECT [DateOf] = [DATE] , [Totals] = SUM(Assets) 
FROM Assets inner join Funds on Assets.FundCode = Funds.FundCode
WHERE FeeGroupID = 17
    and (([Date] >= '1/1/2013') 
    AND ([Date] <= '4/1/2013')) 
    and ((Funds.EndDt >= '4/1/2013') OR (Funds.EndDt is null))
GROUP BY [DATE]
) as MonthlyTotals

Upvotes: 3

Related Questions