SQL Group by Year

Question

This is my query.

select CONVERT(varchar, cast(date as datetime), 3)
from shoptransfer 
group by year (date)

I want to group by the year part of the date (varchar) column, however I get the following error:

Column 'shoptransfer.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How do I group by the year part of the date column?

Answer 1

You should add column shoptransfer.Date into group by clause.

select CONVERT(varchar,cast(date as datetime),3)
from shoptransfer 
where 
SUBSTRING(productcode, 5, 3) like '%' 
group by CONVERT(varchar,cast(date as datetime),3)

Answer 2

I don't know about T-SQL, but in SQL in general, what is in the group by clause must exactly match each non-aggregate function column in the select clause. Try

select CONVERT(varchar,cast(date as datetime),3)
from shoptransfer 
where 
SUBSTRING(productcode, 5, 3) like '%'  group by CONVERT(varchar,cast(date as datetime),3)

also, where SUBSTRING(productcode, 5, 3) like '%' is not filtering out much - maybe remove it.

Answer 3

If you want to select both the date and the year you should not use a GROUP BY clause as it combines all of the rows with similar years into one row. If you want all of the dates with similar years together you can use an ORDER BY:

SELECT DATEPART('yyyy',date) AS Year,date
FROM shoptransfer 

OR

SELECT DATEPART('yyyy',date) AS Year,date
FROM shoptransfer
ORDER BY DATEPART('yyyy',date) desc

Answer 4

How about:

select datepart(yyyy, [date]) as [year]
from shoptransfer 
group by datepart(yyyy, [date])

Or:

select count(*) as qty, datepart(yyyy, [date]) as [year]
from shoptransfer 
group by datepart(yyyy, [date])
order by [year]

This is based on OP's command: "I want to group by year part of date (varchar) column"

Answer 5

group by
substring(CONVERT(varchar,comuln1,101),1,4)

year 2004 2005 2006