• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

sql server 2005 - count syntax

I have this error:

No column was specified for column 1 of 'x'.

in the following codes, but can't firgure out what's wrong, could you please help?

select count(*) from (
SELECT 'Check 2', Par_id, Trend_type, TypeOfContract, DatePart(m,[planeffdt]) AS [Month], DatePart(yyyy,[planeffdt]) AS [YEAR]
FROM TRP.FFS_Contracts
GROUP BY Par_id, Trend_type, TypeOfContract, DatePart(m,[planeffdt]),  DatePart(yyyy,[planeffdt])
HAVING Count(Par_id) > 1
) x
0
HNA071252
Asked:
HNA071252
3 Solutions
 
keyuCommented:
try this

select count(*) from (
SELECT 'Check 2' as check_Field, Par_id, Trend_type, TypeOfContract, DatePart(m,[planeffdt]) AS [Month], DatePart(yyyy,[planeffdt]) AS [YEAR]
FROM TRP.FFS_Contracts
GROUP BY Par_id, Trend_type, TypeOfContract, DatePart(m,[planeffdt]),  DatePart(yyyy,[planeffdt])
HAVING Count(Par_id) > 1
) x
0
 
Scott PletcherSenior DBACommented:
When you use a derived table in SQL Server, you must give every column in that derived table a name, and you must alias the table (which you did in your query as "x").

[Derived table = "(SELECT ...) x"]
0
 
sachitjainCommented:
Solutions given above by Keyu and ScottPletcher are correct, you got to give some alias for 'Check 2' in your query.
0
 
HNA071252Author Commented:
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now