CODEWITHSUNDEEP
sql
Christopher Vickers
Christopher Vickers

Reputation: 1953

Sql error in simple query? Any ideas?

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

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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.

Upvotes: 0

Related Questions