SQL Getting two sums and their diff from two tables

Question

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

Thaxton

What means Otherwise my profit would be negative? I think profit should be negative when you purchased more than sold. – Parthenon

How do you decide which of the 15 units you purchased to use against the 10 you sold? You need a key that connects the units sold to their purchase origin to do this accurately – Kammerer

@Parthenon No. In theory you may be correct that the profit is negative. But that's not the result I want to get. – Thaxton

@PhilCoulson The decision should be done on the date witch is available in the table. This means from the oldest to the newest. – Thaxton

You need to provide the expected results using the sample data. – Pedlar

@Pedlar I've added the desired result for my sample data. – Thaxton

The expected results don't really make sense. You need to provide an explanation of why those are the expected results or how you're arriving at those numbers, so that we can help you reproduce them. – Pedlar

Answer 1

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.

---

Now it's time for MS Access.

I said you need to create new table but I was wrong. You don't need.

I created RowNumber VBA function to mimic row_number SQL function.

1. Create Class Module and name it to CRowNumber and paste following code.

Option Compare Database
Option Explicit

Private PartitionOld As String
Private RowNum As Integer
Private RowNums As New Dictionary

Public Table As String

Public Function RowNumber(Order As String, Partition As String) As Integer
    ' Order is used to prevent duplicated execution
    If RowNums.Exists(Order) Then
        ' Already executed before, so use cached value
        RowNum = RowNums(Order)
    Else
        ' If Partition changed, reset to 1, otherwise increase 1
        If (Partition <> PartitionOld) Then
            RowNum = 1
        Else
            RowNum = RowNum + 1
        End If
        
        'Debug.Print Table & "," & Order & ", " & Partition & "=" & PartitionOld & ":" & RowNum
    
        ' Cache for future use
        RowNums.Add Order, RowNum
    End If
    
    PartitionOld = Partition
    
    RowNumber = RowNum
End Function

2. Create Module and paste following code.

Option Compare Database
Option Explicit

' To use Dictionary:
' [Tools] - [References] - Check [Microsoft Scripting Runtime]
' To clear cache:
' RowNumber "", "", "", True
Public Function RowNumber(Table As String, Order As String, Partition As String, Optional Reset As Boolean) As Integer
    Dim rn As CRowNumber
    Static RowNumbers As New Dictionary
    
    
    If Reset Then
        Set RowNumbers = New Dictionary
        RowNumber = 0
        Exit Function
    End If
    
    If RowNumbers.Exists(Table) Then
        Set rn = RowNumbers.Item(Table)
    Else
        Set rn = New CRowNumber
        rn.Table = Table
        RowNumbers.Add Table, rn
    End If
        
    RowNumber = rn.RowNumber(Order, Partition)
End Function

3. Create Query and paste following SQL.

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

4. I used Dictionary in code to cache previous row number and table, to use Dictionary

• [Tools] - [References] - Check [Microsoft Scripting Runtime]

5. Clear cache by calling RowNumber function in Immediate window before running query.

RowNumber "", "", "", True

6. Run Query

You will see same result with SQL Server version.

You always need to clear cache before running query to use new cache for new query.

Difference between SQL Server and MS Access is

1. row_number replaced to RowNumber VBA function

2. SQL Server only need id, partition for numbering, But VBA function need more: Table, article, id, rnum because VBA function cannot change order of rows.

3. order by repaced to group by to get sub query rows first and join next, otherwise optimizer will try to join first and get sub query rows next.

I am sorry I didn't explain enough about code, but I will answer if you have question about code.

Parthenon

First of all thank you. And yes this is exactly what I want. It would be nice to have a solution for MS access. – Thaxton

Answer 2

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

Barnard

Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3) – Turpitude

Thanks. But this doesn't answer my question. Because if e. g. I purchased 30 articles but only sold 10 articles the resulted profit would be negative. And this is not what I want. – Thaxton