count is wrong for author related book why and how to solve ?

I work on SQL server 2012 I face issue count is wrong for book s related to author

so why count for books related to author is wrong for author Ahmed

create table #books
(
BookId int,
BookName nvarchar(200),
AuthorId int
)
create table #booksUpdate
(
BookId int,
BookName nvarchar(200),
AuthorId int
)
insert into #booksUpdate
values
(119,'matlab',1),
(120,'3dmax',1),
(121,'c',1)
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)
select a.authorName,count(b.BookName) as countBooks , count(bu.BookName) as countBooksUpdate,(count(bu.BookName) + count(b.BookName)) as Total from #Authors a
left join #books b on a.AuthorId=b.AuthorId
left join #booksUpdate bu on a.AuthorId=bu.AuthorId
group by a.authorName

result is wrong for Author Name Ahmed

it must be

so How to fix query to give correct result

1 Reply

Know the answer? Post it — somebody with the same question will find it here.