SQL Getting two sums and their diff from two tables-CodePudding

I'm having an access database for a merchandise system. In this database there are two tables:

table purchases

id  article  date        amount  price
1   341      2022-02-03  3       23
2   343      2022-02-04  5       18
3   343      2022-02-08  7       21
4   345      2022-02-17  3       12
5   348      2022-02-21  8       45
6   341      2022-03-02  5       27

table sales

id  article  date        amount  price
1   343      2022-02-23  3       28
2   341      2022-02-24  5       30
3   341      2022-03-03  2       35
4   345      2022-03-07  3       18

Now I want to determine how much profit I've generated for each article. This means sum of amount * price for an sold article minus the sum of amount * price for the purchases article. The difficult part is, that if I've only sold 10 articles but purchased 15 articles I will need the sum of 10 purchased article and not from the 15. Otherwise my profit would be negative.

I know how I can solve this with a "regular" programming language by iterating through the rows but I'm wondering if this can be done with a single SQL query (e. g. with inner queries or similar).

Any help is appreciated.

Edit: The following would be the desired result for the example from above:

id  article  amount  bought  sold  profit
1   343      3       54      84    30
2   341      7       177     220   43
3   345      3       36      54    18

CodePudding user response：

Try this:

SELECT 
    a.article, a.buy, b.sell, (a.sell-a.buy) profit 
FROM (
    SELECT 
        article, SUM(amount * price) buy
    FROM purchases
    GROUP BY article
) a
INNER JOIN (
    SELECT 
        article, SUM(amount * price) sell
    FROM sales
    GROUP BY article
) b ON a.article = b.article

CodePudding user response：

I understand what you meaning and I wanted to edit but prevented by message 'Suggested edit queue is full'.

So I want to explain what you really want.

You want bought amount to not exceed sold amount. (ex: Article 341 has sold amount of 7 and bought amount of 8, you want to limit amount to 7 for both bought and sold items)
You want to sell first bought items first (FIFO). (ex: Article 341 has been bought 2 times with different amount (3 * 23 first, 5 * 27 second). You want to sell first 7 amount in this order: 3 * 23 4 * 27, not this order: 5 * 27 2 * 23

select * into purchases
from (
select 1 id, 341 article, '2022-02-03' date, 3 amount, 23 price union all
select 2, 343, '2022-02-04', 5, 18 union all
select 3, 343, '2022-02-08', 7, 21 union all
select 4, 345, '2022-02-17', 3, 12 union all
select 5, 348, '2022-02-21', 8, 45 union all
select 6, 341, '2022-03-02', 5, 27
) t;

select * into sales
from (
select 1 id, 343 article, '2022-02-23' date, 3 amount, 28 price union all
select 2, 341, '2022-02-24', 5, 30 union all
select 3, 341, '2022-03-03', 2, 35 union all
select 4, 345, '2022-03-07', 3, 18
) t;

select * into range
from (
select 1 rnum union all
select 2 rnum union all
select 3 rnum union all
select 4 rnum union all
select 5 rnum union all
select 6 rnum union all
select 7 rnum union all
select 8 rnum union all
select 9 rnum union all
select 10 rnum
) t;

select min(s.id) id, s.article, count(s.article) amount,
       sum(p.price) bought, sum(s.price) sold,
       sum(s.price) - sum(p.price) profit
from
(
select row_number() over (partition by p.article order by p.id) seq,
       p.article, p.price
from   purchases p
       inner join range r
       on p.amount >= r.rnum
) p
inner join
(
select row_number() over (partition by s.article order by s.id) seq,
       s.id, s.article, s.price
from   sales s
       inner join range r
       on s.amount >= r.rnum
) s
on p.article = s.article and p.seq = s.seq
group by s.article
order by min(s.id);

Result:

id  article amount  bought  sold    profit
1   343 3   54  84  30
2   341 7   177 220 43
4   345 3   36  54  18

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=96decd2f92a8f77964a929013e825774

This is SQL Server solution. There's no row_number function in MS Access as you know. So you need to create new table to generate seq column with AutoNumber type to mimic row_number in case of MS Access.

If this solves your problem and you want this to be applied to MS Access, let me know it.