Total of Totals in SQL Server-CodePudding

I have 3 tables -

Books -

BookNo BookName  BookType
123    ABC          1
555    XYZ          0

Shelf

Shelf ShelfNo BookNo BookQuantity
  XB   XB01     123    5
  XB   XB02     555    3
  XB   XB03     123    8

BooksIssued

ShelfNo BookName IssuedDate   QuantityIssued
  XB01    ABC      11/21/2022     2
  XB02    XYZ      11/20/2022     1
  XB03    ABC      11/21/2022     5

My goal is to find out total number of books stock we have. The output should be grouped by book. And I have to combine all shelfNo which contain the same book and sum their Shelf.BookQuantity and then add it to BooksIssued.QuantityIssued for that particular book. Booktype should be displayed as Children for 0 and 1 for adults.

For example,

Output

BookNo BookName BookType Total Stock
123     ABC       adults   20        //(5 8 2 5)
555     XYZ      children   4         //(3 1)

So far, I have written this. I know I have chosen extra columns in my query than what I have mentioned in my output format. It is so because I was going step by step to understand the flow. I wanted to first group the data by book and sum the quantity but it isn't grouping the data by bookno . It is also not summing the bi.quantityissued.

select s.bookno, b.booktype, s.shelfno, b.bookname, s.bookquantity, 
    sum(bi.quantityissued), bi.issueddate
from Shelf s 
left outer join BooksIssued bi on s.shelfno = bi.shelfno 
left outer join Books b on s.bookno=b.bookno
where s.shelf = 'XB'
    and bi.issueddate between '11/01/2022' and  '11/07/2022'
group by  s.bookno, s.shelfno,  b.booktype,  b.bookname, s.bookquantity, bi.issueddate

Please guide me what do I do next. Thank you.

CodePudding user response：

This should do it:

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

This also shows one of the reasons (among several) the BooksIssued table should use BookNo instead of BookName: it would save you a join.

We can also write this with nested SELECT queries instead of the common table expressions (CTEs), but I find the CTE much easier to reason about:

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

This is still missing the adults vs children book type. You can fix this with a CASE expression, or using a Table Value Constructor. I prefer the latter here, because you can add many more types over time in an efficient way and because it sets you up to eventually use a real table, which is what you should have had in the first place:

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;

CodePudding user response：

Try this:

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