Menu
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?
406
You simply use the aggregate function (here: SUM ) with the correct column and at the end of the query you group by year . You can rename the column using the AS keyword with a new name. In this solution, you don't use a GROUP BY clause.
1 Answer. You can use DATE_FORMAT operator. If you are using this you can easily group the date, timestamp or datetime column using whatever format you want.
The EXTRACT() function returns a number which represents the year of the date. The EXTRACT() function is a SQL standard function supported by MySQL, Oracle, PostgreSQL, and Firebird. If you use SQL Server, you can use the YEAR() or DATEPART() function to extract the year from a date.
MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155 , and 0000 . YEAR accepts input values in a variety of formats: As 4-digit strings in the range '1901' to '2155' . As 4-digit numbers in the range 1901 to 2155 .
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"
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
