按项目编号和库存数量的总和分组

I am trying to get my head into space on how I can GROUP by the item number and add the qty to stock.

我正在努力深入了解如何按项目编号分组并将数量添加到库存中。

My current code is:

我现在的代码是:

SELECT dbo.InventoryItems.ItemNo, dbo.InventoryItems.DescriptionMed AS Description
     , SUM(dbo.InventoryItems.QtyToStock) AS QtytoStock, dbo.Locations.LocationCode
  FROM dbo.InventoryItems INNER JOIN dbo.Locations
    ON dbo.InventoryItems.LocationID = dbo.Locations.LocationID
 GROUP BY dbo.InventoryItems.ItemNo, dbo.InventoryItems.QtyToStock
     , dbo.InventoryItems.DescriptionMed, dbo.Locations.LocationCode
HAVING (dbo.InventoryItems.ItemNo LIKE 'CL10%')

and I am getting the below result:

我得到的结果如下:

But my expected output is:

但我的预期产出是:

CL1000 will just be in two rows with their sum. Please help!

CL1000将会以它们的和分成两行。请帮助!

ItemNo          Description               QTYtoStock      LocationCode
CL1000          Square Seat Legs             4               CREST
CL1000          Square Seat Legs             93              DZ
CL1002          Square Low Back Sofa         5               DZ

1 个解决方案

#1

You clearly just need the right GROUP BY:

你显然只需要正确的群体:

SELECT ii.ItemNo, ii.DescriptionMed AS Description, 
       SUM(ii.QtyToStock) AS QtytoStock, l.LocationCode
FROM dbo.InventoryItems ii INNER JOIN
     dbo.Locations l
     ON ii.LocationID = l.LocationID
WHERE ii.ItemNo LIKE 'CL10%'
GROUP BY ii.ItemNo, ii.DescriptionMed, l.LocationCode;

All the unaggregated columns (or expressions) should be in the GROUP BY. QtyToStock is being aggregated, so it is not appropriate.

所有未聚合的列(或表达式)都应该位于GROUP BY中。QtyToStock正在聚合，所以不合适。

Further advice:

进一步的建议:

Use table aliases. These should be abbreviations for the tables, so they are easy to follow.
用表的别名。这些应该是表的缩写，所以很容易理解。
Qualify column names with the shortened aliases. Much, much easier to write and read.
用缩短的别名限定列名。写和读起来要容易得多。
The HAVING clause is on a GROUP BY key. This is better handled (usually) using WHERE. The WHERE will reduce the number of rows that need to be aggregated, which is usually a performance win.
have子句按键在一个组上。这是更好的处理(通常)使用的地方。WHERE将减少需要聚合的行数，这通常是性能上的优势。

#1