Итого в SQL Server

Это должно сделать это:

WITH baseData As
(
    SELECT BookNo, BookQty As Qty
    FROM Shelf s
    WHERE s.shelf = 'XB'
   
    UNION ALL 

    SELECT b0.BookNo, QtyIssued As Qty
    FROM BooksIssued bi
    INNER JOIN Books b0 on b0.BookName = bi.BookName
    WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
), 
grouped As
(
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM baseData
    GROUP BY BookNo
)
SELECT b.BookNo, b.BookName, b.BookType, g.[Total Stock]
FROM grouped g
INNER JOIN Books b ON b.BookNo = g.BookNo

Это также показывает одну из причин (среди нескольких), по которой таблица BooksIssued должна использовать BookNo вместо BookName: это избавит вас от соединения.

Мы также можем написать это с помощью вложенных запросов SELECT вместо общих табличных выражений (CTE), но я считаю, что CTE гораздо проще рассуждать:

SELECT b.BookNo, b.BookName, b.BookType, g.[Total Stock]
FROM (
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM (
        SELECT BookNo, BookQty As Qty
        FROM Shelf s
        WHERE s.shelf = 'XB'
       
        UNION ALL 
    
        SELECT b0.BookNo, QtyIssued As Qty
        FROM BooksIssued bi
        INNER JOIN Books b0 on b0.BookName = bi.BookName
        WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
    ) baseData
    GROUP BY BookNo
) g
INNER JOIN Books b ON b.BookNo = g.BookNo

В нем по-прежнему отсутствует тип книги adults vs children. Вы можете исправить это с помощью выражения CASE или с помощью конструктора табличных значений . Я предпочитаю последнее здесь, потому что вы можете добавить гораздо больше типов с течением времени эффективным способом, и потому что это настраивает вас в конечном итоге на использование реальной таблицы, которая у вас должна была быть в первую очередь:

WITH baseData As
(
    SELECT BookNo, BookQty As Qty
    FROM Shelf s
    WHERE s.shelf = 'XB'
   
    UNION ALL 

    SELECT b0.BookNo, QtyIssued As Qty
    FROM BooksIssued bi
    INNER JOIN Books b0 on b0.BookName = bi.BookName
    WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
), 
grouped As
(
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM baseData
    GROUP BY BookNo
)
SELECT b.BookNo, b.BookName, bt.Name As BookType, g.[Total Stock]
FROM grouped g
INNER JOIN Books b ON b.BookNo = g.BookNo
INNER JOIN (VALUES (0, 'Childrens'), (1 'Adults') ) AS bt(Type, Name)   
    ON b.BookType = bt.Type;  

Попробуй это:

select bookno BookNo,
        bookname BookName,
        case booktype
            when 1 then 'adults'
            when 0 then 'children'
        end BookType
        SUM(TotalStock) TotalStock
from (

    select b.bookno,
           b.bookname,
           b.booktype,
           sum(bookquantity) TotalStock
    from ..books b
    inner join ..shelf s
        ON b.bookno = s.bookno
    group by b.bookno, b.bookname, b.booktype
    
    UNION ALL
    
    select b.bookno,
           b.bookname,
           b.booktype,
           sum(QuantityIssued) TotalStock
    from ..books b
    inner join ..shelf s
        ON b.bookno = s.bookno
    inner join ..booksissued bi
        ON s.shelfno = bi.shelfno
            and b.bookname = bi.bookname
    group by b.bookno, b.bookname, b.booktype
) s
group by bookno, bookname, booktype