28

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?

Kev
  • 118,037
  • 53
  • 300
  • 385
Zain Ali
  • 15,535
  • 14
  • 95
  • 108

6 Answers6

74

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"

Kev
  • 118,037
  • 53
  • 300
  • 385
  • What are you trying to achieve with where SUBSTRING(productcode, 5, 3) like '%'? – garnertb May 11 '11 at 12:35
  • @user589983 - no point asking me, it's the OP's query, I just kept his example code as-is but fixed so that it would `GROUP BY`. – Kev May 11 '11 at 12:38
  • I want to select the complete date you are just selecting year part of date. – Zain Ali May 11 '11 at 12:46
  • @zain - your post says `"want to group by year part of date"`, that's what I've shown you how to do. Perhaps you could reword your question and explain exactly what you want. – Kev May 11 '11 at 12:51
  • @zain - sample input data and a sample of what you want to achieve never hurts – Kev May 11 '11 at 12:53
  • your query group by year fine!But it only select year **datepart(yyyy, [date])** not date **CONVERT(varchar,cast(date as datetime),3) **That is my requiremnet – Zain Ali May 11 '11 at 12:54
  • @zain - You can't have both grouped by year AND the individual dates in the result because they're aggregated. – Kev May 11 '11 at 12:56
3

With MariaDB:

SELECT year(date) FROM cost GROUP BY year(date)
Astro
  • 55
  • 1
  • 6
  • 1
    Code-only answers are discouraged. Please explain how this is different from other answers to the same question. – CLAbeel Mar 22 '20 at 17:33
1

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.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Bear. Teddy Bear.
  • 139
  • 1
  • 2
  • 12
1

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
garnertb
  • 9,454
  • 36
  • 38
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)
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
skorpk
  • 424
  • 3
  • 6
0
group by
substring(CONVERT(varchar,comuln1,101),1,4)

year 2004 2005 2006

jack.mike.info
  • 128
  • 3