I try to run an sql query (mssql 2005) like the following:
select top 20 d_date, date1, date2
group by date2
order by date2 desc
and I get the following error:
Column 'reestr_calculated.d_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Does anybody know how can I deal with that error?
When you use
GROUP BY, multiple rows get "collapsed" to one row. To determine which of those rows in a group should be displayed, you have to use an aggregate function.
SUM() or others.
select top 20 MIN(d_date), MAX(date1), date2 from reestr_calculated where reestr_id=2 group by date2 order by date2 desc
or like this:
select top 20 d_date, date1, date2 from reestr_calculated where reestr_id=2 group by d_date, date1, date2 order by date2 desc
All the column names present in
SELECT must be present in
GROUP BY unless you are using aggregate functions. Hence, add date1 also in group by.
You're grouping by
date2, which means returning one row for each
date2 value. Each
date2 value could have multiple
date1 values, so SQL doesn't know which values to return for each one.
As the error says, you either need an aggregate function (
AVG() etc) or to include the other columns in the GROUP BY clause