Query to get current remaining stock in sqlite

Question

I have a table named stock:

name   stock
------------
 a      100
 b      200
 c       50

and a sales table

  name      sale
 --------------------
    a       30
    c       20
    d       30

The result should be:

name      stock
-----------------
   a       70
   b      200
   c       20
   d      -30

Please tell me the sqlite query for this.

My query:

select a.name, a.stock - b.sales as stock 
from stock as a 
inner join Sale as b on a.name = b.name

but how to get those name which doesn't exists in stock but exists in sales?

This requites a [full outer join](https://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite). — CL., Feb 10 '18 at 09:15
https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query Pleasse provide your table in SQLite syntax, i.e. "create ..." and some "insert ...". Then show what you have tried and explain what does not satisfy you. — Yunnosch, Feb 10 '18 at 09:18
Sample data in SQLite syntax please, if you do that work, instead of expecting answerers to do it for testing purposes, you will find more favorable reactions. — Yunnosch, Feb 10 '18 at 09:27

MikeT · Answer 1 · 2018-02-10T09:49:22.390

I believe the following will do what you want :-

SELECT name, sum(stock) - sum(sold) FROM
(
    SELECT name, sold, 0 as stock FROM sales
    UNION ALL
    SELECT name, 0 AS sold, stock FROM stock
)
GROUP BY name
ORDER BY name

Basically the inner SELECT extracts a table with 3 columns name, sold and stock for each combination and with 0 as the missing value. i.e. :-

This is then used as the source of the outer SELECT which groups according to name summing the sales and the stock and subtracting the sales from the stock as per :-

Query to get current remaining stock in sqlite

1 Answers1