CODEWITHSUNDEEP
mysqlyii2
dungphanxuan
dungphanxuan

Reputation: 615

Count Inventory of Product

I have table Bill Detail for store Product infor.

When i input product with price and input with repository and quantity.

And i get output product by get product id with repository and quantity

Now i need count inventory of product in repository.

Example:

Bii Detail Data:

Type    Product_ID Repository_id Qty
1       2          2             1
1       2          2             3
2       2          2             1
1       2          3             2
1       2          3             3
2       2          3             2

Note: Type: 1: Input, 2: output

Folow data: Total Inventory of product 2 in repository 2 has: 

  Total Input - Total output = (1+3) - 1 = 3

I need sql for: - List all repository has product: Example with product 2, has 2 repository 2, and 3 - Total Inventory of product with repository: Example, with product 2 and repository 3. Total Inventory is 3.

So how can i using SQL for query?

Upvotes: 0

Views: 101

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

In SQL
You could use a sum and group by and case when based on type 

  select 
      Product_ID
    , Repository_id
    , sum(case when type = 1 then Qty 
               when typ2 = 2 then -Qty
          END) total
  from my_table 
  group by       
      Product_ID
    , Repository_id

Upvotes: 2

Related Questions