By Ben
The required system requirements of a software project:
There is a data sheet:
Name quantity state
-----------------------
Product a 10
Product a 20 sales
Product B 20
To query the result:
Product name stock quantity
-----------------------
Product a -10
Product B 20
Solution 1: SELECT
Distinct name,
(ISNULL (SUM (a. Quantity) from inventory a WHERE A. Name = inventory table. Name and A. State = 'In stock'), 0)
-
ISNULL (SELECT SUM (quantity) from inventory a Where A. Name = inventory table. Name and A. Status = 'Sales'), 0) AS stock quantity
From
Inventory
Solution 2: SELECT Name, SUM (IIF (Status = 'Injection', 1, -1) * Quantity) AS Stock Quantity from inventory GROUP BY Name
Solution 3 (Access):
SELECT Name, SUM (IIF (Status = 'Injection ", Quantity, 0) -iif (Status =' Limited", Quantity, 0) AS Stock Quantity from inventory GROUP BY Name
