问题描述:

I try to run an sql query (mssql 2005) like the following:

select top 20 d_date, date1, date2

from reestr_calculated

where reestr_id=2

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.

Like MIN() or SUM() or others.

Like this:

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 d_date or date1 values, so SQL doesn't know which values to return for each one.

As the error says, you either need an aggregate function (MAX(), MIN(), AVG() etc) or to include the other columns in the GROUP BY clause

相关阅读:
Top