SQL Server - Select query error

Question

I got an error

Column 'books.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Code:

select Name, MIN(Date) as Date, Pub 
from books
where Date is not null
group by Pub

I would like to display Name of the old book and its publisher name.

@TabAlleman this has to be one of the most popular errors posted on StackOverflow — WhatsThePoint, Mar 27 '18 at 13:49
Sample data and desired results would help a lot here. I think @TabAlleman is onto something with his answer, but it's not completely clear. — Eric Brandt, Mar 27 '18 at 14:42

score 6 · Answer 1 · answered Mar 27 '18 at 13:21

6

Every non-aggregate columns should be there in the GROUP BY clause.

Try this:

select Name, MIN(Date) as Date, Pub from books
where Date is not null
group by Name, Pub

answered Mar 27 '18 at 13:21

DineshDB

5,998
7
33
49

score 2 · Answer 2 · answered Mar 27 '18 at 13:22

2

Name should be wrapped with an aggregate function, or you maybe want do like this:

select Name, MIN(Date) as Date, Pub from books
where Date is not null
group by Pub, Name

that means, you should group by Name too.

Hope it's helpful.

answered Mar 27 '18 at 13:22

Nothing Mi

232
1
8

score 1 · Answer 3 · answered Mar 27 '18 at 13:23

Your deal with aggregations in your query. Columns can be part of aggregation calculation, like MIN(Column1), COUNT(*) or so. In other cases column has to be specified in a GROUP BY clause

So, Name column to be added to a group by clause:

select Name, MIN(Date) as Date, Pub from books
where Date is not null
group by Pub, Name

score 1 · Answer 4 · answered Mar 27 '18 at 13:49

1

I would like to display Name of the old book and its publisher name.

Did you mean "oldest book"?

If that's the case, you don't do this with MIN and GROUP BY.

You do it with TOP 1 and ORDER BY.

answered Mar 27 '18 at 13:49

Tab Alleman

31,483
7
36
52

My table consists of columns: Id, Name, Price, Pub, Pages, Date, Pressrun. Like: 5, Corel Draw for professionals, 100, DiaSoft, 500, 2001-01-01 00:00:00:000. Now I want to show the Name of publishers and the oldest book from it, that is why I find min of date. But when I group all publishers, I cannot simply add name of those old books. – Mar 27 '18 at 18:31
Create a new question, and word it this way: "I want to show the Name of publishers and the oldest book from it." Your original question was poorly worded and that's why it got closed. – Tab Alleman Mar 27 '18 at 18:33

SQL Server - Select query error

4 Answers4