How to count all books related to all authors ?

Question

I work on SQL server 2012 I face issue I can't count books for all author
How to count book for all authors exist
this is my data

create table #books
(
BookId int,
BookName nvarchar(200),
AuthorId int
)
create table #Authors
(
AuthorId int,
AuthorName nvarchar(200)
)
insert into #Authors
values
(1,'Ahmed'),
(2,'Mohamed'),
(3,'Eslam')
insert into #books
values
(122,'c#',1),
(233,'Java',1),
(555,'c++',1),
(666,'photoshop',2),
(777,'asp.net',2),
(888,'python',2)

AuthorName countBooks
Ahmed 3
Mohamed 3
Eslam 0

Answer 1

One way is with a LEFT JOIN, so that authors without books are included. Note that COUNT(b.BookId) will count only non-null BookId values for each author.

SELECT
      a.AuthorName
    , COUNT(b.BookId) AS countOfBooks
FROM #Authors AS a
LEFT JOIN #Books AS b ON b.AuthorId = a.AuthorId
GROUP BY a.AuthorName;

Answer 2

SELECT a.AuthorName
     ,Isnull(b.countOfBooks,0) countOfBooks
 FROM #Authors AS a
 LEFT JOIN (SELECT AuthorId, COUNT(BookId) AS countOfBooks 
 FROM #Books GROUP BY AuthorId) AS b ON b.AuthorId = a.AuthorId;

Answer 3

Hi ahmedsalah，

Please try:

   select a.AuthorName,count(b.AuthorId) countbooks  
    from #books b  
    right join #Authors a  
    on a.AuthorId=b.AuthorId  
    group by AuthorName  
    order by countbooks desc  

 

If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo