SQL Group by Year

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 f开发者_开发知识库unction or the GROUP BY clause.

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

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"

With MariaDB:

SELECT year(date) FROM cost GROUP BY year(date)

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

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.

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)

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

year 2004 2005 2006