What is wrong with my "group by" code in SQL?

January 15, 2022

FEEDBACK table:

CREATE TABLE Feedback 
(
    FeedbackID nvarchar(50) NOT NULL PRIMARY KEY, 
    Rating SMALLINT, 
    Comments nvarchar(50), 
    MemberID nvarchar(50) FOREIGN KEY REFERENCES Member(MemberID), 
    BookID NVARCHAR(50) FOREIGN KEY REFERENCES Book(BookID)
);

BOOK table:

CREATE TABLE Book  
(
    BookID nvarchar(50) NOT NULL PRIMARY KEY, 
    Name nvarchar(50), 
    SerialNumber nvarchar(50), 
    CategoryID nvarchar(50) FOREIGN KEY REFERENCES Category(CategoryID), 
    PublisherID NVARCHAR(50) FOREIGN KEY REFERENCES Publisher(PublisherID), 
    Unit_Price nvarchar(50), 
    Quantity NVARCHAR (50)
);

I have only managed to get this by using

SELECT BookID, COUNT(FeedbackID) AS Numoffeedback 
FROM Feedback 
GROUP BY BookID;

BookID | TotalNumberofFeedbacks
-------+-----------------------

But my desired output is this :

BookID | Name | TotalNumberofFeedbacks
-------+------+-----------------------

I tried do this

SELECT BookID, COUNT(FeedbackID) AS Numoffeedback 
FROM Feedback 
GROUP BY BookID, Name;

but i received this error "Invalid column name ‘Name’.

>Solution :

You need to join the two tables together and then aggregate by book. You may try the following query:

SELECT b.BookID, b.Name, COUNT(f.BookID) AS TotalNumberOfFeedbacks
FROM Book b
LEFT JOIN Feedback f
    ON f.BookID = b.BookID
GROUP BY b.BookID, b.Name;

Note that we count f.BookID here. The reason for this is should a certain book have no feedback, we want the count to be zero.