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

Question

I work on SQL Server 2012 and I face an issue: count is wrong for book s related to author. Why is count for books related to author is wrong for author Ahmed?

When count author ahmed I get an incorrect result although it has related records in the books table as 3 and bookupdate 3 then all will be 6.

How to solve that please?

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

Output:

authorName  countBooks  countBooksUpdate    Total  
--------------------------------------------------
Ahmed            9              9             18  
Eslam            0              0              0  
Mohamed          3              0              3

Result is wrong for author Ahmed.

It should be

authorName  countBooks  countBooksUpdate    Total 
-------------------------------------------------
Ahmed            3                 3          6

How to fix query to return the correct result?

Expected result is

authorName  countBooks  countBooksUpdate    Total  
-------------------------------------------------
Ahmed           3             3                6  
Eslam           0             0                0  
Mohamed         3             0                3

user_6748758 · Answer

This works. It was missing the join on BookId between #books and #booksUpdate

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  
                                  and b.BookId=bu.BookId
group by a.authorName;

Results

authorName  countBooks  countBooksUpdate    Total
Ahmed       3           0                   3
Eslam       0           0                   0 
Mohamed     3           0                   3

The BookId's in the #booksUpdate table do not match any in the #books table. Without the join on BookId the query is performing a cross join.

Suppose the 3 rows in #booksUpdate were changed to match 3 rows for Ahmed. Here I'm changing the data posted in the question.

drop table if exists #booksUpdate;
go
create table #booksUpdate  
(  
BookId int,  
BookName nvarchar(200),  
AuthorId  int  
)  
insert into #booksUpdate  
values  
(122,'matlab',1),  
(233,'3dmax',1),  
(555,'c',1)

Then the result would be as follows:

authorName  countBooks  countBooksUpdate    Total
Ahmed       3           3                   6
Eslam       0           0                   0
Mohamed     3           0                   3

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

1 Answers