February 10, 2010 at 7:44 am
T-SQL 2000 newbie working with string concatenation and receiving an error message for a Group By...
select 'Year ending '+cast(year(job_date) as varchar(4))
+' revenue was '+cast(sum(job_cost) as varchar(20))
from job
group by cast(year(job_date) as varchar(4));
Msg 8120, Level 16, State 1, Line 1
Column 'job.job_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The job_date is formatted as datetime and job_cost as money. I thought at first it had to do with me performing a CAST at the Group By but the same error message showed up when I dropped the CAST. I also found that when I remove the string concatenation the syntax works fine so what am I doing wrong?? Any help is appreciated.
February 10, 2010 at 8:12 am
Try this way:
select 'Year ending '+ job_date
+' revenue was '+ job_cost
from (
select cast(year(job_date) as varchar(4)) as job_date,
cast(sum(job_cost) as varchar(20)) as job_cost
from job
group by cast(year(job_date) as varchar(4))
) AS data
-- Gianluca Sartori
February 10, 2010 at 8:36 am
Thanks Gianluca, the syntax worked exactly as I needed.
February 10, 2010 at 8:55 am
It's interesting to note that your original syntax doesn't fail in SQL 2005.
Very strange.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply