SQL calculate number of books and list it from different tables-CodePudding

Couldn't describe it properly in a title so I'll try to do it here:

My goal is to calculate amount of books by their ID's (each of them has a different ID, but there are several books that have more than one copy) and list them like this - Title, ISBN, Number of copies in the DB.

It would work easily if Title, ISBN, and ID of each book would be in the same table, but they are not, so it doesn't work out for me. The ISBN is present in both tables. Below is my code:

SELECT COUNT(A.ID), A.ISBN, B.Title    
FROM Books AS A, Book AS B     
WHERE A.ISBN = B.ISBN     
GROUP BY A.ISBN;

Each time I try to fix it it tells me what could I try to fix it, but then it comes up with another error and I'm afraid I will go too deep with it. Any advice?

P.S. The error I get most of the time is "B.Title must appear in the GROUP BY clause or be used in an aggregate function" and it doesn't really work trying to fix it.

CodePudding user response：

ONLY_FULL_GROUP_BY needs that all columns have to be in the Group BY or have to use a aggregation function

But even then you get double counts, so i added another condition

Also use JOIN syntax as it is already aeound for 30 years

SELECT COUNT(A.ID), A.ISBN, MAX(A.Title)    
FROM Books AS A LEFT JOIN Book AS B ON A.ISBN = B.ISBN AND A.ID < B.ID    
GROUP BY A.ISBN

CodePudding user response：

Include all the columns you need in select to form the group, I also recommend that you use "JOINS"

Try this:

SELECT COUNT(A.ID), 
       A.ISBN, 
       B.Title    
FROM Books AS A
Join Book AS B ON A.ISBN = B.ISBN     
GROUP BY A.ISBN, B.Title;

***Sorry for my english I'm learning