Column is invalid in the select list

Question

SELECT * 
FROM Book b 
WHERE b.book_id IN (SELECT DISTINCT( c.book_id ) 
                    FROM Copy c 
                    WHERE c.copy_id IN (SELECT copy_id 
                                        FROM Loan 
                                        WHERE Datediff(DAY, Getdate() - 180, loan_date) > 180) 
                    GROUP  BY c.copy_id 
                    HAVING Count(copy_id) > 10)

When I ran this query I'm getting below error.

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

possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:38

shA.t · Answer 1 · 2015-04-04T16:16:49.677

When you want to have a field after select in a select statement with group by part you should add its name after group by too and don't use distinct keyword, I think you need a code like this:

SELECT c.book_id  
FROM   Copy c 
WHERE  c.copy_id IN (SELECT copy_id 
                 FROM   Loan 
                 WHERE  Datediff(DAY, Getdate() - 180, loan_date) > 180) 
GROUP  BY c.book_id
HAVING Count(copy_id) > 10

score 0 · Answer 2 · answered Apr 04 '15 at 15:24

You are grouping by c.copy_id, yet, you want to select c.book_id. This is invalid. You might consider one option from the following:

group by c.book_id
group by c.book_id besides c.copy_id
don't use group by in the inner query
select c.copy_id and use it in the outer select as well

I believe the very first option makes the most sense in this case.

score 0 · Answer 3 · answered Apr 04 '15 at 15:31

The problem causing the error is that you're grouping on the wrong column in the subquery, it should be b.book_id.

Then I think you're datediff comparison might have the dates in the wrong order (but that depends on your intent and I might have misunderstood what you're trying to do).

So with the changes maybe this query might be what you're looking for:

SELECT * FROM Book b 
WHERE b.Book_Id IN (
    SELECT c.Book_id
    FROM Copy c 
    JOIN Loan l ON c.copy_id = l.copy_id
    WHERE DATEDIFF(DAY, Loan_date, getdate()-180) > 180
    GROUP BY c.Book_Id 
    HAVING COUNT(l.Copy_Id) > 10
)

Column is invalid in the select list

3 Answers3