Sql error in simple query? Any ideas?

Question

I'm getting the error:

Column 'A10000012VICKERS.dbo.IMAGES.idimage' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas why I would be getting this error or how to fix it? I thought that I was just asking for the size of a number of filestream columns and the values of two others?

SELECT
idimage,
filetype,
SUM(DATALENGTH(filestreamimageoriginal)) AS original,
SUM(DATALENGTH(filestreamimagefull)) AS [full],
SUM(DATALENGTH(filestreamimageextra)) AS extra,
SUM(DATALENGTH(filestreamimagelarge)) AS large,
SUM(DATALENGTH(filestreamimagemedium)) AS medium,
SUM(DATALENGTH(filestreamimagesmall)) AS small, SUM(DATALENGTH(filestreamimagethumbnail)) AS thumbnail
FROM A10000012VICKERS.dbo.IMAGES WHERE display = 1

Did you search for the error? This question has been asked many many many times before. The error message is also pretty self-explanatory — HoneyBadger, Nov 11 '16 at 11:14
Possible duplicate of [SQL query with avg and group by](http://stackoverflow.com/questions/10702546/sql-query-with-avg-and-group-by) — Mr. Bhosale, Nov 11 '16 at 11:18
MySQL's GROUP BY extensions allow you to execute aggregate functions without using a group by. This is NOT standard behavior on most relational databases systems. Normally anything in a select NOT using an aggregate should be in the group by. In MySQL's case it will select an indeterminate value for idimage and fileyype and use it (performance benefit). if all the values for idImage and filetype are the same this presents no problem, however if they are different and you want to see the idimage and filetype for each records, then you must add those to fields to the group by. — xQbert, Nov 14 '16 at 15:07
[LInk 5.7](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) to MySQL group by and the GROUP BY extension feature or [5.6 version](https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html) which are different in the implementation/execution. — xQbert, Nov 14 '16 at 15:10

Gordon Linoff · Answer 1 · 2016-11-14T14:59:22.650

1

I don't really see how that query could generate that message. There is no column with that name. However, the query does have an obvious error.

Your query is an aggregation query because it uses SUM() in the SELECT clause. However, this will return only one row, unless you also have a GROUP BY.

Add this to the end of your query:

GROUP BY idimage, filetype

Or, remove these columns from the SELECT.

edited Nov 14 '16 at 14:59

answered Nov 11 '16 at 11:13

Gordon Linoff

1,242,037
58
646
786

Thank you. It works. Why do I have to add the 'Group By' for this? – Christopher Vickers Nov 11 '16 at 11:15
Gordon, your speed is legendary, but can you include an explanation of why including `SUM` by itself puts the query into `GROUP BY` mode? – Tim Biegeleisen Nov 11 '16 at 11:16
@TimBiegeleisen . . . Isn't it obvious that including an aggregation function (such as `SUM()`) turns the query into an aggregation query? – Gordon Linoff Nov 14 '16 at 14:55
@GordonLinoff It isn't obvious actually. The presence of `GROUP BY` is obvious that an aggregation is taking place. And MySQL allows using aggregate functions without `GROUP BY` (though perhaps not standard behavior). Just looking for a sage explanation. – Tim Biegeleisen Nov 14 '16 at 14:56

score 0 · Answer 2 · answered Nov 11 '16 at 11:34

By using an aggregation function (SUM) you are aggregating your records. As you have specified no GROUP BY clause you will get one result row, i.e. an aggregation over all rows. In this aggregation, however, there is no longer one idimage or one filetype that you could show in your results.

So either use an aggregation function on these, too (e.g. max(idimage), min(filetype)) or remove them from the query, if you really want one aggregate over all these rows.

If, however, you want to aggregate per idimage and filetype, then add GROUP BY idimage, filetype at the end of your query.

Sql error in simple query? Any ideas?

2 Answers2