Data | The query system requirements of a software project for statistical authors:
Data table: Name Quantity status
-----------------------
Product a 10 purchase
Product a 20 sales
Product b 20 Incoming query results are: Product Name inventory quantity
-----------------------
Product A-10
Product B 20 Solution One:
SELECT
DISTINCT name,
(ISNULL (select SUM (A. quantity) from inventory table a WHERE A. name = Inventory table. Name and A. Status = ' incoming '), 0
-
ISNULL (select SUM (A. quantity) from inventory table a WHERE A. name = Inventory table. Name and A. Status = ' sales '), 0) as inventory quantity
From
Inventory table
Workaround Two (Access):
SELECT name, Sum (IIF (status = ' incoming ', 1,-1) * quantity) as inventory quantity from inventory table Group by name
Workaround Three (Access):
SELECT name, Sum (IIF (state = ' incoming ', quantity, 0)-iif (status = ' Sales ', quantity, 0)) as Inventory quantity from inventory table Group by name
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
