SQL - Adding values from same column of different IDs

Question

I have 2 tables product and inventory as shown below.

Product:

Product_ID     Product_Description    Price
100            Computer               30
200            Laptop                 50
300            Printer                10

Inventory:

Product_ID   Stock
100          40
200          15
300          50

From these both tables, I need a query to list Product ID, Product Description and quantity which has the maximum number of stock and add the stock of laptop to the display.

Expected Output:

Product_ID   Product_Description   Stock
300          Printer               65

The 65 is the result of 50 for printer & 15 for laptop.

Could you please help with a query for this at the earliest?

Answer 1

You can do it with MAX() aggregate function:

SELECT p.Product_ID, p.Product_Description, 
       MAX(CASE WHEN p.Product_Description = 'Laptop' THEN i.Stock END) + 
       MAX(i.Stock) Stock
FROM Product p INNER JOIN Inventory i
ON i.Product_ID = p.Product_ID 

This code relies in SQLite's feature to return the row that contains the max value (in this case the last defined max).

See the demo.
Results:

Product_ID	Product_Description	Stock
300	Printer	65