如何通过项目单元获得最终数量组?

I have book stock table like :

我有这样的书库存表:

---------------------------------------------------------------------
|   BOOK_ID     |   WAREHOUSE   |   UNITS   |   QTY_IN  |   QTY_OUT |
---------------------------------------------------------------------       
|       1       |       W01     |   PCS     |     5     |     0     | 
---------------------------------------------------------------------
|       2       |       W02     |   BOX     |     1     |     0     |
---------------------------------------------------------------------
|       1       |       W01     |   PCS     |     20    |     0     |
---------------------------------------------------------------------
|       1       |       W01     |   BOX     |     2     |     0     |
---------------------------------------------------------------------
|       1       |       W01     |   PCS     |     0     |     2     |
---------------------------------------------------------------------

I want to get final quantity for each book item, eg:

我想要每一本书的最终数量，例如:

for BOOK_ID 1 TOTAL QTY IS 2 BOX, 23 PCS
对于BOOK_ID 1 TOTAL QTY是2个BOX, 23个pc。
for BOOK_ID 2 TOTAL QTY is 1 BOX
对于BOOK_ID 2 TOTAL QTY是一个BOX。
so on..
等等. .

This is I had tried so far :

这是我迄今为止尝试过的:

select BOOK_ID, UNITS, WAREHOUSE, sum(QTY_IN) as QTY_IN, sum(QTY_OUT) as QTY_OUT from 
(select BOOK_ID, UNITS, WAREHOUSE, QTY_IN, QTY_OUT
from BOOK_STOCK) d
group by BOOK_ID, UNITS, WAREHOUSE

Please help me how to grouping and sum the quantity? Thank you.

请帮助我如何分组和合计数量?谢谢你！

2 个解决方案

#1

Use case expressions to do conditional aggregation:

用例表达式来做条件聚合:

select BOOK_ID,
       sum(case when units = 'BOX' then QTY_IN - QTY_OUT else 0 end) as QTY_BOX,
       sum(case when units = 'PCS' then QTY_IN - QTY_OUT else 0 end) as QTY_PCS
from BOOK_STOCK
group by BOOK_ID

#2

Below code should do the trick for you.

下面的代码应该可以帮你解决这个问题。

 select BOX.book_id, BOX.TotalQty as BoxFinalQuantity, PCS.TotalQty as PCSFinalQuantity
from 
(
select book_id, units, sum(qty_in-qty_out) as TotalQty from #Books
where units like 'BOX'
group by book_id, units) BOX
full outer join (
select book_id, units, sum(qty_in-qty_out) as TotalQty from #Books
where units like 'PCS'
group by book_id, units) PCS ON BOX.book_id = Pcs.book_id

#1