Grouping by partial value

I'm very new to SQL and I have no clue how to even begin with this one. I've got two tables: Warehouse and Items. Here's how they look like(simplified):

Warehouse

ItemID | QuantityInStock | QuantityOnOrder | QuantityOnOrder2 | QuantityOnOrder3 | QuantityOnOrder4
-------+-----------------+-----------------+------------------+------------------+-----------------
1111   |               8 |               1 |                0 |                1 |               0 
2222   |               3 |               0 |                0 |                0 |               0 
3333   |               4 |               0 |                1 |                0 |               0 

Items

ItemID | Code
-------+-----------------
  1111 | abc123456-111-01
  2222 | abc123456-111-02
  3333 | abc123457-112-01 

What I need to return via SQL query is this:

ShortCode | Quantity
----------+---------
abc123456 | 9
abc123457 | 3

• ItemID is the key to join both tables
• Code in the Items table include main product code 开发者_运维百科(abc123456) and variants (-111-01). I need to group the lines by main product code only
• Quantity I need comes from Warehouse table and it equals "QuantityInStock - QuantityOnOrder - QuantityOnOrder2 - QuantityOnOrder3 - QuantityOnOrder4". Using this we get abc123456 (comes in two variants in Items table with ItemId 1111 and 2222) and Quantity is equal 8 minus 1 minus 0 minus 1 minus 0 for 1111, and 3 minus 0 minus 0 minus 0 minus 0 for 2222 which together gives 9

This is probably the worst explanation ever, so I hope there is someone that can understand it.

Please help.

Assuming that you can always count on matching the first 9 characters of the Code column then the following query should work.

/// note that the SUM method may return a negative (-) number
SELECT LEFT(I.[Code], 9) AS 'ShortCode', SUM([QuantityInStock] - [QuantityOnOrder] - [QuantityOnOrder2] - [QuantityOnOrder3] - [QuantityOnOrder4]) AS 'Quantity'
FROM [dbo].[Warehouse] AS W
INNER JOIN [dbo].[Items] AS I ON I.[ItemId] = W.[ItemId]
GROUP BY LEFT(I.[Code], 9)

Using standard SQL:

SELECT 
  LEFT(Items.Code, 9) AS ShortCode, 
  SUM(T.remaining) AS Quantity 
FROM Items
JOIN ( 
  SELECT 
    ItemID, 
    QuantityInStock - QuantityOnOrder - QuantityOnOrder2 - QuantityOnOrder3 - QuantityOnOrder4 AS remaining 
  FROM Warehouse
) AS T ON (T.ItemID = Items.ItemID)
GROUP BY LEFT(Items.Code, 9);

Not tested, but should work. Only potential issue is that you use uppercase letters in your table and column names, so you might have to enclose all table and column names in backticks (`) or square brackets depending on your DB server.

EDIT: If you want to filter those with less than a certain number of pieces left, just add:

HAVING SUM(T.remaining) > xxx

Where xxx is the minimum quantity you want